Search varchar data in all column SQL server 2008

Hi All,

 

in this blog we will search the text in all the column in the particular database, for that we will use below script

 

DECLARE @SearchStringValue NVARCHAR(100)='Enter your text here';

DECLARE @Results AS TABLE
(
ColumnName VARCHAR(1064) ,
ColumnValue VARCHAR(MAX)
)

SET NOCOUNT ON

DECLARE
@Tname NVARCHAR(256) ,
@Cname NVARCHAR(128) ,
@StringValue VARCHAR(1064)
SET @Tname = ''
SET @StringValue = '''%' + @SearchStringValue + '%'''
--,''''

WHILE @Tname IS NOT NULL
BEGIN
SET @Cname = ''
SET @Tname = ( SELECT
MIN(TABLE_SCHEMA + '.' + TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
TABLE_SCHEMA + '.' + TABLE_NAME > @Tname
)

WHILE ( @Tname IS NOT NULL ) AND
( @Cname IS NOT NULL )
BEGIN
SET @Cname = ( SELECT
MIN(COLUMN_NAME)
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA = PARSENAME(@Tname, 2) AND
TABLE_NAME = PARSENAME(@Tname, 1) AND
DATA_TYPE IN ( 'varchar' ) AND
COLUMN_NAME > @Cname
)

IF @Cname IS NOT NULL
BEGIN
INSERT INTO @Results
EXEC
( 'SELECT ''' + @Tname + '.' +
@Cname + ''', LEFT(' + @Cname +
', 3630)
FROM ' + @Tname + ' WHERE ' + @Cname + ' LIKE ' +
@StringValue
)
END
END
END

SELECT
ColumnName ,
ColumnValue
FROM
@Results