Well as I said before I am extending FluentMigrator to Migrate ArcSDE tables as well as regular tables. For that (especially in ArcSDE 10) I need to known which table is Sde’s and which is pure SQL. In 9.3.1 it was fairly easy all you have to do is check if the table has an OBJECTID column – but in 10 that field is gone for non geographic SDE tables – JOY!
So the first thing I did is write a query that returns all the SDE tables in my DB:
SELECT [database_name]
,[table_name]
,[owner]
,[rowid_column]
FROM [sde].[sde].[SDE_table_registry]
where database_name like 'DATABASE_NAME'
order by table_name asc
The regular tables query is a bit more complex, I used the system view [INFORMATION_SCHEMA].[TABLES] where I first removed all the “Numbered tables” and then removed all the geographic tables (using the above query):
SELECT [TABLE_CATALOG]
,[TABLE_SCHEMA]
,[TABLE_NAME]
,[TABLE_TYPE]
FROM [DATABASE_NAME].[INFORMATION_SCHEMA].[TABLES]
WHERE NOT(TABLE_NAME LIKE '%1%') AND
NOT(TABLE_NAME LIKE '%2%') AND
NOT(TABLE_NAME LIKE '%3%') AND
NOT(TABLE_NAME LIKE '%4%') AND
NOT(TABLE_NAME LIKE '%5%') AND
NOT(TABLE_NAME LIKE '%6%') AND
NOT(TABLE_NAME LIKE '%7%') AND
NOT(TABLE_NAME LIKE '%8%') AND
NOT(TABLE_NAME LIKE '%9%') AND
NOT(TABLE_NAME LIKE '%0%') AND
TABLE_NAME NOT IN (SELECT [table_name] AS TABLE_NAME
FROM [sde].[sde].[SDE_table_registry]
where database_name like 'DATABASE_NAME')\
Keywords: ESRI, ArcSDE, SDE, SQL Server, Table, Geographic table
