Global Database Field Search

,

Search all text fields in a database for a literal string.
Outputs SQL statements to query analyser window, which can then be run in a new window.

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

Rate

Share

Share

Rate