I use the following stored procedure to search for Text in a database. This works for medium sized databases in SQL Server. Sometimes in analyzing an existing database you need to find where a value resides. I wrote this to assist with that. Use at your own risk.
Create Procedure FindTextWithTempTable
@searchterm varchar(50),
@ignoreTable varchar(100) = '',
@exactMatch bit = 0
as
Begin
IF @exactMatch = 0
BEGIN
set @searchterm = ' like ''%' + @searchTerm + '%'''
END
ELSE
BEGIN
set @searchterm = ' = ''' + @searchTerm + ''''
END
Declare @sql varchar(max)
Declare @QueryText varchar(max)
Declare @tempsql varchar(max)
Declare @RowCounter int
declare @tmp table
(
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[FIELD_DATA] varchar(max) NOT NULL,
[FIELD_NAME] varchar(100) NOT NULL,
[TABLE_NAME] varchar(100) NOT NULL
)
Declare QueryCursor CURSOR FAST_FORWARD FOR
Select CASE
WHEN DATA_TYPE = 'TEXT' Then
'SELECT CONVERT(varchar,[' + COLUMN_NAME + ']) as FIELD_DATA ' + ' , ''' + COLUMN_NAME + ''' as [FIELD], '''
+t.TABLE_NAME + ''' as [TABLE] FROM [' + t.TABLE_NAME
+ '] where CONVERT(varchar,[' + column_name + ']) ' + @searchTerm
WHEN DATA_TYPE = 'float' Then
'SELECT CONVERT(varchar,[' + COLUMN_NAME + ']) as FIELD_DATA ' + ' , ''' + COLUMN_NAME + ''' as [FIELD], '''
+ t.TABLE_NAME + ''' as [TABLE] FROM [' + t.TABLE_NAME
+ '] where CONVERT(varchar,[' + column_name + ']) ' + @searchTerm
WHEN DATA_TYPE = 'numberic' Then
'SELECT CONVERT(varchar,[' + COLUMN_NAME + ']) as FIELD_DATA ' + ' , ''' + COLUMN_NAME + ''' as [FIELD], '''
+ t.TABLE_NAME + ''' as [TABLE] FROM [' + t.TABLE_NAME
+ '] where CONVERT(varchar,[' + column_name + ']) ' + @searchTerm
ELSE
'SELECT [' + COLUMN_NAME + '] as FIELD_DATA ' + ' , ''' + COLUMN_NAME + ''' as [FIELD], '''
+ t.TABLE_NAME + ''' as [TABLE] FROM [' + t.TABLE_NAME
+ '] where [' + column_name + '] ' + @searchTerm
END as QueryText from INFORMATION_SCHEMA.COLUMNS
INNER JOIN INFORMATION_SCHEMA.TABLES t on INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = t.TABLE_NAME
Where DATA_TYPE in ('varchar','nvarchar','text')
and Lower(t.TABLE_NAME) != Lower(@ignoreTable)
OPEN QueryCursor
FETCH NEXT FROM QueryCursor
INTO @QueryText
Set @RowCounter = 1
set @sql = '';
WHILE @@FETCH_STATUS = 0
BEGIN
-- do row specific stuff here
Insert into @tmp (FIELD_DATA,FIELD_NAME,TABLE_NAME)
exec(@QueryText)
FETCH NEXT FROM QueryCursor
INTO @QueryText
END
CLOSE QueryCursor
DEALLOCATE QueryCursor
Select * from @tmp
order by FIELD_DATA
Select COUNT(*) as ValueInstanceCount, TABLE_NAME,FIELD_NAME
from @tmp
Group By TABLE_NAME,FIELD_NAME
Order By TABLE_NAME
END
examples of how I used it
Examples
execute jtFindTextWithTempTable ‘F3′
this looks for all but ignores the table AccountTransactions
execute jtFindTextWithTempTable ‘F3′, ‘AccountTransactions’
this looks for all but ignores the table AccountTransactions and only looks for exact matches of the text
execute jtFindTextWithTempTable ‘F3′, ‘AccountTransactions’,1
Advertisement
