The code below 1 can be used to find all tables that contain columns that have “ADDRESS” in the name. It can easily be modified to search for table column names with any specific word.
CASE O.xtype WHEN 'U' THEN 'Table' WHEN 'V' THEN 'View' END AS Type,
O.name AS TableName,
C.name AS ColumnName
FROM SYSCOLUMNS C
INNER JOIN SYSOBJECTS O
ON C.ID = O.ID
WHERE C.name LIKE '%ADDRESS%'
AND O.xtype IN ('U', 'V')
ORDER BY O.xtype, O.name, C.ColOrder
- Please ensure you test any script taken from my website on a test/development machine, before running on a production server. ↩