posted 5/19/2010 by MikeMollenhour - Views: [710]
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:
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'''It will then return column names where it was found.It will then return column names where it was found.
sp_msforeachtable 'Exec [dbo].[usp_FindSearchTerminTableColumns] @tableName = ''?'', @SearchTerm = ''7484 Roundtree Drive'''
It will then return column names where it was found.