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)

Share

Share

Rate

4.86 (7)