SQL Server: Search a string|text in all the tables, rows, columns | CodeAsp.Net

If you want to search a string|text in all the tables, rows, columns below script will help you:

DECLARE @SearchText VARCHAR(100) ,
    @TableName SYSNAME ,
    @TableID INT ,
    @ColumnName SYSNAME ,
    @Command VARCHAR(2000)

SET @SearchText = 'product'

DECLARE SearchCursor CURSOR
FOR
    SELECT  name ,
            object_id
    FROM    sys.objects
    WHERE   type = 'U'

OPEN SearchCursor

FETCH NEXT FROM SearchCursor INTO @TableName, @TableID

WHILE ( @@FETCH_STATUS = 0 ) 
    BEGIN
        DECLARE ColumnsCursor CURSOR
        FOR
            SELECT  name
            FROM    sys.columns
            WHERE   object_id = @TableID
                    AND system_type_id IN 
                    ( 167, 175, 231, 239 ) -- VARCHAR, CHAR, NVARCHAR, NCHAR

        OPEN ColumnsCursor

        FETCH NEXT FROM ColumnsCursor INTO @ColumnName
        WHILE ( @@FETCH_STATUS = 0 ) 
            BEGIN
                SET @Command = 'IF EXISTS (SELECT * FROM ' + @TableName
                    + ' WHERE [' + @ColumnName + '] LIKE ''%'
                    + @SearchText + '%'') PRINT ''' + @TableName + ', '
                    + @ColumnName + ''''

                EXECUTE(@Command)

                FETCH NEXT FROM ColumnsCursor INTO @ColumnName
            END

        CLOSE ColumnsCursor

        DEALLOCATE ColumnsCursor

        FETCH NEXT FROM SearchCursor INTO @TableName, @TableID
    END

CLOSE SearchCursor

DEALLOCATE SearchCursor

You just need to set "SearchText" variable and run the script to get your results.