Find all tables containing column with specified name - MS SQL Server

Fast answer

Enriching the search with schema details

In some cases, a bit of extra information never hurts. If you're on a quest for more than just table names, enlist schema details to the rescue:

-- SQL query, or as I call it, coding Sudoku without those annoying numbers
SELECT SCHEMA_NAME(t.schema_id) + '.' + t.name AS FullTableName, c.name AS ColumnName
FROM sys.tables AS t
INNER JOIN sys.columns AS c ON t.object_id = c.object_id
WHERE c.name LIKE '%myColumn%'
ORDER BY FullTableName, ColumnName;


Going broad: Including views with INFORMATION_SCHEMA

For exploring both tables and views, your trusted companion is INFORMATION_SCHEMA.COLUMNS:

-- Don't forget to save your work, folks!
-- Captain Obvious ... Away! 🦸
SELECT COLUMN_NAME, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%myColumn%'
ORDER BY TABLE_NAME, COLUMN_NAME;


Remember, this cross-DBMS method plays nice with different SQL databases too. A versatile tool in your SQL utility belt.

Deep Dive into INFORMATION_SCHEMA

The craft of detailed exploration

Tailor your queries to bring forth more architecture-specific attributes, like data types or non-null requirements:

-- SQL: it's like fetch quests in video games, but the townsfolk pay you in data instead of gold.
SELECT 
    C.TABLE_SCHEMA, 
    C.TABLE_NAME, 
    C.COLUMN_NAME, 
    C.DATA_TYPE, 
    C.CHARACTER_MAXIMUM_LENGTH, 
    C.IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS AS C
WHERE C.COLUMN_NAME LIKE '%myColumn%'
ORDER BY C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME;


Picking your database toolbox

While sys.tables and sys.columns are SQL Server's home ground, INFORMATION_SCHEMA presents a one-size-fits-all solution kind of a Sonic Screwdriver for the Whovians among us.

Unleashing the metadata power of system views

To get your hands on revelatory metadata like modification dates or identity status, unlock the capabilities of SQL Server's system views:

-- Fun fact: SQL Server and C# walk into a bar...
-- Wait, no, that's not a fun fact, that's the start of a joke. Never mind.
SELECT 
    t.name AS TableName, 
    c.name AS ColumnName, 
    t.create_date, 
    t.modify_date, 
    c.is_identity, 
    c.is_computed
FROM sys.tables AS t
INNER JOIN sys.columns AS c ON t.object_id = c.object_id
WHERE c.name LIKE '%myColumn%'
ORDER BY TableName, ColumnName;


Non-nullable columns: For when NULL just won't do

Set eyes on mandatory data fields by fish-netting those non-null columns:

-- Because NULL is the SQL equivalent of "meh"
SELECT t.name AS TableName, c.name AS ColumnName
FROM sys.tables AS t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.name LIKE '%myColumn%' AND c.is_nullable = 0
ORDER BY TableName, ColumnName;


Answer Upgrade: Beyond the basics

Pinpoint precision with exact matches

Perhaps you want to search for exact names instead of patterns. Fear not, for we have a solution!

-- SQL: Turning caffeine into code since 1972
SELECT t.name AS TableName
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.name = 'ExactColumnName'


Exploring the schema landscape

Extend the radar to scan across sundry schemas by roping in sys.schemas in your investigation:

-- Know your data, know your enemy. Sun Tzu probably didn't say this about SQL, but he could have.
SELECT s.name AS SchemaName, t.name AS TableName, c.name AS ColumnName
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE c.name LIKE '%myColumn%'
ORDER BY SchemaName, TableName, ColumnName;


Prioritizing your search results

Sometimes, all columns aren't equal, and you want to bring specific ones to the top of your list. Enter, CASE statements:

-- It's like SQL's version of 'sorting hat'. "Hmm... difficult. Very difficult. COLUMN_NAME = 'PrimaryInterest'... You could be... in Priority 1!"
SELECT t.name AS TableName, c.name AS ColumnName,
CASE WHEN c.name = 'PrimaryInterest' THEN 1 ELSE 2 END AS Priority
FROM sys.tables AS t
JOIN sys.columns AS c ON t.object_id = c.object_id
WHERE c.name LIKE '%myColumn%'
ORDER BY Priority, TableName, ColumnName;
