Jan
14
2015
I didn't write this, as usual I found it on Stack Overflow and other places. I had a need to search a SQL database for tables, views, and columns that contained certain key words. This has been very helpful.
Declare @ObjectName VarChar (100)
Set @ObjectName = '%post%'
Select 'QTR_CC_SEA' as DatabaseName, [name] as ObjectName, Type as ObjectType
From QTR_CC_SEA.Sys.Objects
Where 1=1
and [Name] like @ObjectName
and Type in ('U', 'P')
SELECT COLUMN_NAME, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE @ObjectName
AND COLUMN_NAME not like 'tbl_aud%'