SQL: Finding table column names with a specific word

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.

SELECT 
	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
  1. Please ensure you test any script taken from my website on a test/development machine, before running on a production server.

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.