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 
