Wednesday, January 19, 2011

Today I had to analyze a foreign database. During research I had the need to Lookup a value from a PrimaryKey somewhere in the Database. Because I did not know the whole Database Model, I thought about using a Full Text Search to easily find where the expected Value maybe was used (linked in). Because enabling FullTextSearch in the Database Model for each table was to time consuming, I wrote this handy little routine. Feel free to use it or you may want to save it as a Stored Procedure with the search Pattern as input parameter.

Replace your SearchText at the @SearchPattern variable. If you want to find the SearchText somewhere in a field, put a % sign at the beginning. For Example: set @SearchPattern='%house'

Declare @s varchar(max)
Declare @SearchPattern varchar(max)
Declare @unionAll bit

--- 0 = UNION                 es werden keine doppelten Einträge aufgelistet
--- 1 = UNION ALL           
set @unionAll=0
set @SearchPattern='Suchtext'

select @s=@s+'select '''+table_name+''' as TableName,'''+COLUMN_NAME+''' as DataColumn,['+column_name+'] collate SQL_Latin1_General_CP1_CI_AS as Text from '+Table_Name+' where ['+column_name+'] like '''+@SearchPattern+'%'' '+
char(13)+char(10) + case when @unionAll=1 then 'union all' else 'union' end +char(13)+char(10) from information_schema.columns where Data_type like '%char'
and table_name <> 'dtproperties'
--- If you don't want lookup Views also .. remove remarks
---and TABLE_NAME in (select TABLE_NAME from
set @s=left(@s,len(@s)- case when @unionAll=1 then 12 else 7 end)

execute ( @s )

« Schneeschuhtour Neujahr 2011, Lämmernhüt... | Main | Hamburg Ausflug mit Musicals Cats und Kö... »