Because GIS schemas normally use consistent nomenclature between classes, many users can find a good use for finding a specific data criterion within all of them. Most DBMS software solutions like SQL Server and Oracle provide built-in functionality to explore this concept, but Esri geodatabases also include SDE management tables to make the process even easier.

This article provides the resources to retrieve the names of any database table that contains the field name you specify. Our clients have presented many scenarios where this script is useful – for example, a script that could help you output install dates of any asset, or one that shows you every table that records operating area location codes.

More advanced database aficionados can use this data to programmatically run scripts by plugging the table and column names into new queries, which can be run using variables and the exec() function. Even without that knowledge, though, the data can still be put to great use in its raw list form. So grab your favorite query tool and let’s get those queries going!

Geodatabase Method (preferred)

Our primary approach to this method is to avoid the vanilla tables and use the SDE management tables included with any Esri geodatabase. This method only pulls table names from within the geodatabase itself, and will not retrieve any unregistered tables. Replace the underlined example column name (“INSTALLDATE”) in the following scripts with your own desired name, keeping it in uppercase format.

Oracle:

select OWNER, TABLE_NAME, COLUMN_NAME from SDE.COLUMN_REGISTRY where column_name = ‘INSTALLDATE‘;

SQL Server:

select OWNER, TABLE_NAME, COLUMN_NAME from SDE.SDE.SDE_COLUMN_REGISTRY where column_name = ‘INSTALLDATE‘;

The results will look like this:

DBMS Methods

An alternative method of attack is via our Database Management System – either SQL Server or Oracle in these examples. These methods are technically more versatile at the cost of potentially containing diluted data, as tables outside a geodatabase are included as well. Keep in mind that system tables can change in later versions of DBMS software. Replace the underlined example column name (“INSTALLDATE”) in the following scripts with your own desired name, keeping it in uppercase format.

Oracle:

select o.OWNER, c.TABLE_NAME, o.object_id, c.COLUMN_NAME
from all_tab_columns c
inner join all_objects o
on c.table_name = o.object_name
and o.object_type = ‘TABLE’
where UPPER(c.column_name) = ‘INSTALLDATE‘;

SQL Server:(must be run in a specific database – replace instances of “DB” with the desired database name.)

select s.name as OWNER, o.name as TABLE_NAME, o.object_id, c.name as COLUMN_NAME
from DB.sys.all_columns c
inner join DB .sys.all_objects o
on c.object_id = o.object_id
and o.type = ‘U’
inner join DB.sys.schemas s
on o.schema_id = s.schema_id
where UPPER(c.name) = ‘INSTALLDATE‘;

The results will look like this:

You may notice that if your data is versioned, add/delete tables will show up. If you have created Multiversion views for your data, you can search through those instead:

Oracle:

select o.OWNER, c.TABLE_NAME, o.object_id, c.COLUMN_NAME
from all_tab_columns c
inner join all_objects o
on c.table_name = o.object_name
and o.object_type = ‘VIEW’
where UPPER( c.column_name) = ‘INSTALLDATE‘;

SQL Server: (must be run in a specific database – replace instances of “DB” with the desired database name.)

select s.name as OWNER, o.name as TABLE_NAME, o.object_id, c.name as COLUMN_NAME
from DB.sys.all_columns c
inner join DB .sys.all_objects o
on c.object_id = o.object_id
and o.type = ‘V’
inner join DB .sys.schemas s
on o.schema_id = s.schema_id
where UPPER(c.name) = ‘INSTALLDATE‘;

This query will only attract views, which include Multiversion views. It still may have the benefit (or disadvantage) of pulling any views from outside the geodatabase as well, but it will no longer contain the add/delete tables for your versioned data.

Using one of these scripts, you will end up with a list of table names that you may use for analysis or further processing. The scripts are useful in a variety of scenarios – finding all values of a field across the geodatabase to create a common domain, planning geometric networks, determining the tables that track use modification dates, and a variety of other creative problem- solving scenarios.