Stored Procedure to search for a particular string within an entire database.

IF OBJECT_ID('GetTbColvalues') IS NOT NULL DROP PROC GetTbColvalues
GO

CREATE PROCEDURE GetTbColvalues (
@txtvalue varchar(128) = null
)
AS
DECLARE
@execstr varchar(1000),
@objectname sysname,
@colname sysname

SET NOCOUNT ON
IF @txtvalue IS NULL
BEGIN
RAISERROR ('You must specify the value to search', 16, 1)
RETURN
END

DECLARE tb_fetch_cursor CURSOR FOR
SELECT name FROM sysobjects WHERE type = 'U'
OPEN tb_fetch_cursor
FETCH NEXT FROM tb_fetch_cursor INTO @objectname
WHILE (@@fetch_status <> -1)
BEGIN
DECLARE col_fetch_cursor CURSOR FOR
SELECT name FROM syscolumns WHERE id = OBJECT_ID(@objectname) AND type IN
(SELECT type FROM systypes WHERE name = 'char' OR name = 'nchar' OR name = 'varchar'
OR name = 'nvarchar' OR name = 'text' OR name = 'ntext')
OPEN col_fetch_cursor
FETCH NEXT FROM col_fetch_cursor INTO @colname
WHILE (@@fetch_status <> -1)
BEGIN
SELECT @execstr = 'IF EXISTS (SELECT * FROM ' + @objectname + ' WHERE ' + @colname + ' like ''%' + @txtvalue + '%'') BEGIN SELECT ''' + @objectname + ''' as tbname, ''' + @colname + ''' as colname PRINT '''' END'
EXEC (@execstr)
FETCH NEXT FROM col_fetch_cursor INTO @colname
END
DEALLOCATE col_fetch_cursor
FETCH NEXT FROM tb_fetch_cursor INTO @objectname
END
DEALLOCATE tb_fetch_cursor
GO