Search all tables and views for column name in SQL Server

A nifty little bit of code I’ve used for years and years, very useful if you don’t have a third party add-on installed…

USE AdventureWorks2017

SELECT * FROM (
	SELECT t.name AS name, 
	'table' as type,
	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 '%Employee%'

	UNION

	SELECT v.name AS name,
	'view' as type,
	SCHEMA_NAME(schema_id) AS schema_name,
	c.name AS column_name
	FROM sys.views AS v
	INNER JOIN sys.columns c ON v.OBJECT_ID = c.OBJECT_ID
	WHERE c.name LIKE '%Employee%'
) a
ORDER BY schema_name, name