SQL Script to Find Column from all tables in a SQL Server database

I’m working with huge databases these days.  I’m new to these databases, and it is impossible for me to get familiar with every table and every column right away.   From time to time, people ask me to do some queries based on certain column.  They just tell me the approximate column name.  I need to find out what table contains that particular column in the first place.

Here is the SQL script that saves my effort.  It tells you what table contain certain column given the column name.

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%EmployeeID%'
ORDER BY schema_name, table_name;

You can replace ‘EmployeeID’ in the above script with your column name.  Here is an example output of the script.

image

Leave a Reply