Wednesday, March 16, 2011

Find all tables containing particular column

There are two ways to retrieve all the tables containing a particuler column

1) using INFORMATION_SCHEMA



SELECT * FROM INFORMATION_SCHEMA.COLUMNS As i WHERE i.column_name LIKE '%MyField%'


2) using SYS.TABLES



SELECT * FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%MyField%'



If you know the exact column name, then use = instead of LIKE to reduce the number of unrelated rows return

No comments:

Post a Comment