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