I'm proud of what I do.

Just a quick post to show, how to loop through all tables and columns in database and find all occurrences of the keyword specified -

/*Declare variables */
Declare @Keyword varchar(200)
Declare @CountTable int
Declare @Loop int
Declare @DbOwner varchar(50)

Set @Loop = 1

/*keyword to search for */
Set @Keyword = report

/*Owner of the object for e.g. dbo.TableName */
Set @DbOwner = ‘dbo.’

/* Temp table to store list of all user tables and column names*/
Declare @UserTables table
(
[Id] int identity(1,1),
[TableName] varchar(300),
[ColumnName] varchar(300),
[DataType] varchar(50),
[Length] int
)

/*Retrieving list of Table Name along with Column, Datatype and length to store in @UserTables table */
Insert into @UserTables
(
[TableName],
[ColumnName],
[DataType],
[Length]
)

Select
sysobjects.name,
syscolumns.name,
systypes.name,
syscolumns.length
From
Sysobjects Join SysColumns on sysobjects.id = syscolumns.id
Join Systypes on syscolumns.xtype = systypes.xtype
Where
/* filtering table list to retrieve only user created tables */
sysobjects.xtype = ‘u’

/* Since we will search string keyword, so will take only text column types */
and systypes.name IN (‘varchar’,'nvarchar’,'ntext’,'text’)

/*Removing extra unwanted table */
and sysobjects.name <> ‘dtpproperties’
order by sysobjects.name, syscolumns.colid

/*Retrieving total user tables records count */
Set @CountTable = (Select @@ROWCOUNT)

Create table #Result
(
Id int identity(1,1),
TableName varchar(300),
ColumName varchar(200),
Result varchar(2000)
)

While @Loop < @CountTable
Begin

Declare @TableName varchar(300)
Declare @ColName varchar(200)
Declare @SqlQuery nvarchar(1000)
Select @TableName = TableName, @ColName = ColumnName from @UserTables where Id = @Loop

Set @SqlQuery = ‘Insert into #Result ‘
Set @SqlQuery = @SqlQuery + ‘ Select ”’ + @TableName + ”’,”’ + @ColName + ”’,’ + @ColName + ‘ from ‘ + @DbOwner + @TableName
Set @SqlQuery = @SqlQuery + ‘ where ‘ + @ColName + ‘ like ”%’ + @Keyword + ‘%”’

/*Executing the SQL Query */
execute SP_ExecuteSql @SqlQuery

Set @Loop = @Loop + 1

End

/*Display final list of table and column where keyword is found */
Select * from #Result

/* Cleanup */
Drop table #Result