If you are unable to determine what table you need to look for a certain item, but you know the name of the field, you can use the script below to try and find the Table.
SELECT distinct c.name AS 'ColumnName'
,t.name AS 'TableName'
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%[FieldName]%'
ORDER BY ColumnName;
Simply replace [FieldName] with the column you are looking for and the script will pull up all tables that contain that field. If you are unsure of the exact name of the column, but know some of the words in the column name, you can put a ‘%’ wildcard between each word and the script will pull up all tables and fields with those words.
For instance running the following script in a Sedona Office database will pull up results for both the customer_id and the customer_cc_id.
SELECT distinct c.name AS 'ColumnName'
,t.name AS 'TableName'
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%Customer%id%'
ORDER BY ColumnName;
