Search All Column Names In Sql Server Tables For A String / Pattern

sql-find-all-columns-with-string.sql

use Northwind
go

-- SQL run against Northwind sample database to find all tables
-- that have CustomerID in their column / field names.

-- replace CustomerID in between % % with whatever it is you are searching for

select distinct tab.name + '.' + col.name as 'Table.ColumnName'
from sys.tables tab 
join sys.columns col on (tab.object_id = col.object_id)
join sys.types types on (col.system_type_id = types.system_type_id) 
where tab.type_desc ='USER_TABLE' 
and col.name like '%CustomerID%'
and types.name IN ('CHAR', 'NCHAR', 'VARCHAR', 'NVARCHAR')