Global Database Field Search

CREATE PROCEDURE sp_sys_CheggerVader 
@StringToFind VARCHAR(20)
AS
-----------------------------------------------------------
--  Paul Widdecombe, 10/02/04
--  paul.widdecombe at connells.co.uk
--  Global Search Stored procedure
--  Prints Sql statements in the query analyser window
--  Syntax - exec sp_sys_CheggerVader 'srchstring'
-----------------------------------------------------------

DECLARE @TableName VARCHAR(50)
DECLARE @FieldName VARCHAR(50)
DECLARE @SQLString VARCHAR(150)

DECLARE tables_cursor CURSOR FAST_FORWARD FOR 
select
o.name as TableName,
c.name as FieldName
from 
syscolumns c 
inner join sysobjects o on c.id = o.id
inner join systypes t on c.xtype = t.xtype
where
o.type = 'U' and o.[name] not in ('dtproperties')
and t.name in ('char', 'text', 'varchar', 'nchar', 'ntext', 'nvarchar')
order by TableName

OPEN tables_cursor

FETCH NEXT FROM tables_cursor INTO @TableName, @FieldName

PRINT 'declare @RECORDS INT'

WHILE @@FETCH_STATUS = 0
BEGIN
set @SQLString = 'SELECT @RECORDS = COUNT(*) FROM ' + @TableName + ' WHERE [' + @FieldName + '] like ''%' + @StringToFind + '%'''

print 'SET @RECORDS = 0'
print @SQLString
print 'IF @RECORDS > 1 PRINT ''SELECT * FROM ' + @TableName + ' WHERE ' + @FieldName + ' LIKE ''''%' + @StringToFind + '%'''''''

FETCH NEXT FROM tables_cursor INTO @TableName, @FieldName
END

CLOSE tables_cursor
DEALLOCATE tables_cursor
GO