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
and [Name] like @ObjectName
and Type in ('U', 'P')
SELECT COLUMN_NAME, TABLE_NAME
WHERE COLUMN_NAME LIKE @ObjectName
AND COLUMN_NAME not like 'tbl_aud%'