Parsing an entire SDE database searching for a field for export

Question

I'm wanting to parse through and entire SDE database (feature dataset, features classes, tables, everything...) searching for a field (compkey) and export the numbers to a txt file.

Answer 1

If your data is not versioned (or you've compressed and updated the base tables), you could just use SQL to query out the IDs.

For a manual, one-time-only, type of query, I've used this SQL query (ORACLE) to generate a second SQL query. You could replace the COUNT(*) as ROWCOUNT with a COMPKEY and add additional where clause conditions (you will probably want to change OWNER ='SDE' to OWNER IN ('SCHEMA1','SCHEMA2','SCHEMA3') if you have GIS data in multiple schemas).

select 
    'SELECT COUNT(*) as ROWCOUNT,'''|| t.owner||'.'||t.TABLE_NAME||''' as ClassName FROM '||t.OWNER||'.'||t.TABLE_NAME||' UNION' as SelectStatement 
from sde.table_registry t
where t.owner = 'RNS_RCL'
and t.owner||'.'||t.TABLE_NAME in (select OWNER||'.'||TABLE_NAME  
from all_tab_cols where column_name = 'COMPKEY' and OWNER ='SDE'
order by t.table_name

So the above query will generate a list of select statements with a 'UNION' at the end.

copy/paste that into a new query window (delete the 'UNION' from the last line). and that will give you your list. If you are using SQL Developer or Toad, those applications have built-in capability to export to text file.

If your data is versioned, you should probably look at building multi-versioned views for the versioned featureclasses. Assuming that you would be querying from each featureclass/table at the same version, you could do the same sort of union query but using the multi-version views.