SQL SERVER – Query to Find Column From All Tables of Database

Below script will return all the tables containing specific column along with their schema name.

USE Master

GO

SELECT t.name AS table_name,

SCHEMA_NAME(schema_id) AS schema_name,

c.name AS column_name

FROM sys.tables AS t

INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

WHERE c.name LIKE '%NAME%'

ORDER BY schema_name, table_name