Technical Article

Search all tables for text

,

There have been many times I've been looking for a string or a mention of a string inside of a table, sometimes remembering what field it could be stored in is a big task when you have a massive database. I saw some code on this website about a year ago that did a search feature but I felt it didn't do it the way I wanted, so I cooked up my own version that at the end provides me with a list of all the tables that the string was found in.

 

This searches all the text/string type fields in the table, so if you had a table that had 6 fields, 5 varchar's and 1 int, it would search the 5 varchars individually, ie

 

select * from MyTable where StringField1 like '%query%' or StringField2 like '%query%' or StringField3 like '%query%' or StringField4 like '%query%' or StringField5 like '%query%'

 

To use it:

Step 1. Change the word "territory" to the word you are looking for.

Step 2. Set the return results to Text (it can run in Grid mode however it Management Studio will generate too many errors as it returns the results) or CTRL+T

Step 3. Execute

As shown in the image, you'll see the select statement it used to find that data. What fields were queried, and if the generated string is too long (tables with really long field names) it breaks it into extra SQL statements that it runs individually also.

 

This script is still under development but I've often found this to be the case when writing SQL, you'll always find a better way of doing it later on. It was written under SQL 2005 and am pretty sure I have tested it in SQL 2000

I hope that this script is of use to people, I have used it on many occasions and found it to be a treat.

 

Cheers

 

Luis Chiriff

/*
- Search through tables to find specific text
- Written by Luis Chiriff (with help from SQL Server Central)
- luis.chiriff@gmail.com @ 24/11/2008 @ 11:54
*/
-- Variable Declaration

Declare @StringToFind VARCHAR(200), @Schema sysname, @Table sysname, @FullTable int, @NewMinID int, @NewMaxID int,
@SQLCommand VARCHAR(8000), @BaseSQLCommand varchar(8000), @Where VARCHAR(8000), @CountCheck varchar(8000) , @FieldTypes varchar(8000),
@cursor VARCHAR(8000), @columnName sysname, @SCn int, @SCm int
Declare @TableList table (Id int identity(1,1) not null, tablename varchar(250))
Declare @SQLCmds table (id int identity(1,1) not null, sqlcmd varchar(8000))
Declare @DataFoundInTables table (id int identity(1,1) not null, sqlcmd varchar(8000))


-- Settings

SET @StringToFind = 'territory'
SET NOCOUNT ON
SET @StringToFind = '%'+@StringToFind+'%'

-- Gathering Info

if ((select count(*) from sysobjects where name = 'tempcount') > 0)
drop table tempcount

create table tempcount (rowsfound int)
insert into tempcount select 0

-- This section here is to accomodate the user defined datatypes, if they have
-- a SQL Collation then they are assumed to have text in them.
SET @FieldTypes = ''
select @FieldTypes = @FieldTypes + '''' + rtrim(ltrim(name))+''',' from systypes where collation is not null or xtype = 36
select @FieldTypes = left(@FieldTypes,(len(@FieldTypes)-1))

insert into @TableList (tablename) 
select name from sysobjects 
where xtype = 'U' and name not like 'dtproperties' 
order by name

-- Start Processing Table List

select @NewMinID = min(id), @NewMaxID = max(id) from @TableList

while(@NewMinID <= @NewMaxID)
Begin

SELECT @Table = tablename, @Schema='dbo', @Where = '' from @TableList where id = @NewMinID

SET @SQLCommand = 'SELECT * FROM ' + @Table + ' WHERE' 
-- removed ' + @Schema + '.

SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME
FROM [' + DB_NAME() + '].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ''' + @Schema + '''
AND TABLE_NAME = ''' + @Table + '''
AND DATA_TYPE IN ('+@FieldTypes+')'
--Original Check, however the above implements user defined data types --AND DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'')'

EXEC (@cursor)

SET @FullTable = 0
DELETE FROM @SQLCmds

OPEN col_cursor   
FETCH NEXT FROM col_cursor INTO @columnName   

WHILE @@FETCH_STATUS = 0   
BEGIN   
   
SET @Where = @Where + ' [' + @columnName + '] LIKE ''' + @StringToFind + ''''
SET @Where = @Where + ' OR'

--PRINT @Table + '|'+ cast(len(isnull(@Where,''))+len(isnull(@SQLCommand,'')) as varchar(10))+'|'+@Where

if (len(isnull(@Where,''))+len(isnull(@SQLCommand,'')) > 3600)
Begin
SELECT @Where = substring(@Where,1,len(@Where)-3)
insert into @SQLCmds (sqlcmd) select @Where
SET @Where = ''
End

FETCH NEXT FROM col_cursor INTO @columnName   
END   

CLOSE col_cursor   
DEALLOCATE col_cursor 

if (@Where <> '')
Begin
SELECT @Where = substring(@Where,1,len(@Where)-3)
insert into @SQLCmds (sqlcmd) 
select @Where --select @Table,count(*) from @SQLCmds
End

SET @BaseSQLCommand = @SQLCommand

select @SCn = min(id), @SCm = max(id) from @SQLCmds
while(@SCn <= @SCm)
Begin

select @Where = sqlcmd from @SQLCmds where ID = @SCn

if (@Where <> '')
Begin

SET @SQLCommand = @BaseSQLCommand + @Where
SELECT @CountCheck = 'update tempcount set rowsfound = (select count(*) '+ substring(@SQLCommand,10,len(@SQLCommand)) + ')'
EXEC (@CountCheck) 

if ((select rowsfound from tempcount) > 0)
Begin
PRINT '--- ['+cast(@NewMinID as varchar(15))+'/'+cast(@NewMaxID as varchar(15))+'] '+@Table + ' ----------------------------------[FOUND!]'
--PRINT '--- [FOUND USING:] ' +@SQLCommand
insert into @DataFoundInTables (sqlcmd) select @SQLCommand
EXEC (@SQLCommand) 
update tempcount set rowsfound = 0
End
else
Begin
PRINT '--- ['+cast(@NewMinID as varchar(15))+'/'+cast(@NewMaxID as varchar(15))+'] '+@Table
End
End

SET @SCn = @SCn + 1
End

set @NewMinID = @NewMinID + 1
end

if ((select count(*) from sysobjects where name = 'tempcount') > 0)
drop table tempcount

/*

This will now return all the sql commands you need to use

*/

select @NewMinID = min(id), @NewMaxID = max(id) from @DataFoundInTables

if (@NewMaxID > 0)
Begin
PRINT ' '
PRINT ' '
PRINT '-----------------------------------------'
PRINT '----------- TABLES WITH DATA ------------'
PRINT '-----------------------------------------'
PRINT ' '
PRINT 'We found ' + cast(@NewMaxID as varchar(10)) + ' table(s) with the string '+@StringToFind
PRINT ' '

while(@NewMinID <= @NewMaxID)
Begin

select @SQLCommand = sqlcmd from @DataFoundInTables where ID = @NewMinID

PRINT @SQLCommand

SET @NewMinID = @NewMinID + 1
End

PRINT ' '
PRINT '-----------------------------------------'

End

Rate

4.86 (7)

You rated this post out of 5. Change rating

Share

Share

Rate

4.86 (7)

You rated this post out of 5. Change rating