Finding a particular search term in table columns

Recently I had a report to duplicate in SSRS and had issues trying to find where a particular field was in the database. Since getting the requirements seemed very difficult I figured it would be easier to create a proc that could search for this text in all columns. So this lead me to write the following proc.

---------------------------------------------------------------------------------------------------------------------------------------------------------------

Create Proc [dbo].[usp_FindSearchTerminTableColumns]

(@tableName Varchar(200)

,@SearchTerm varchar(400)

)

AS

Declare @colList table

(id int identity ,ColName Varchar(200)

)

Insert @colList

(

ColName

)

Select c.name

from sys.syscolumns c

join sys.systypes t on t.xtype =c.xtype and t.name like '%char%'

where id= OBJECT_ID(@tableName)

declare @minID int

,@maxID int

,@sqlStatement Varchar(max)

Select @minID =MIN(id), @maxID = MAX(id) from @colList

While @minID <=@maxID

begin

select @sqlStatement = 'IF Exists (Select 1 from ' + @tablename +  ' Where '+ ColName + '=''' + @SearchTerm  + ''') Select '' ' + colname +'''' from @colList where id=@minid

exec (@sqlStatement)

Print @sqlStatement

set @minID =@minID + 1

end

------------------------------------------------------------------------------------------------------------------------------------------------------------------

After creating the proc it can be launched via the following syntax:

EXEC [usp_FindSearchTerminTableColumns]
@tableName = 'Person.Address'
,@SearchTerm = '7484 Roundtree Drive'

you can also use the sp_msforeachtable proc to have it walk through all tables in the database:

sp_msforeachtable 'Exec [dbo].[usp_FindSearchTerminTableColumns]
  @tableName = ''?'',
  @SearchTerm = ''7484 Roundtree Drive'''