15 February 2016

Find a column name across all tables on SQL Server

I'm doing a "black box" project, where I need to discover how a vendor app's database works. To that end, I've found this T-SQL useful in searching across the 140 tables for a column name, thanks to the tip from Pinal Dave's site,
USE myDatabase
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 '%ColumnNameToFind%' 
ORDER BY schema_name, table_name;

No comments: