Виж това за идеи:

IF OBJECT_ID('usp_SearchTables') IS NOT NULL DROP PROC usp_SearchTables

GO

-- exec dbo.usp_SearchTables 'wqw%'



CREATE PROC usp_SearchTables (

            @SearchText     VARCHAR(1000)

) AS

SET         NOCOUNT ON



DECLARE     @SQL            NVARCHAR(4000)

            , @ParamList    NVARCHAR(4000)

            , @TableName    SYSNAME



SET         @ParamList = '@SearchText VARCHAR(1000)'



--- prepare temp table w/ columns/tables to be processed

SELECT      c.COLUMN_NAME, c.TABLE_NAME

INTO        #TmpCol

FROM        INFORMATION_SCHEMA.COLUMNS c

JOIN        INFORMATION_SCHEMA.TABLES t

ON          c.TABLE_NAME = t.TABLE_NAME

WHERE       t.TABLE_TYPE IN ('BASE TABLE') -- 'VIEW'

            AND DATA_TYPE IN ('VARCHAR', 'NVARCHAR', 'CHAR', 'NCHAR') -- 'TEXT', 'NTEXT'



--- loop columns/tables

WHILE       EXISTS (SELECT * FROM #TmpCol)

BEGIN

            SELECT      TOP 1 @TableName = TABLE_NAME

            FROM        #TmpCol



            SET         @SQL = NULL



            --- construct WHERE clause

            SELECT      @SQL = CASE WHEN @SQL IS NULL THEN '' ELSE @SQL + ' OR ' END

                            + QUOTENAME(COLUMN_NAME) + ' LIKE @SearchText'

            FROM        #TmpCol

            WHERE       TABLE_NAME = @TableName



            --- construct IF EXISTS statement

            SET         @SQL = '

IF          EXISTS (    SELECT *

                        FROM        ' + QUOTENAME(@TableName) + '

                        WHERE       ' + @SQL + ')

            RAISERROR (''Found in ' + REPLACE(@TableName, '''', '''''')  + ''', 10, 1) WITH NOWAIT'



--          PRINT       @SQL

            EXEC        dbo.sp_executesql @SQL, @ParamList, @SearchText

            

            DELETE      #TmpCol

            WHERE       TABLE_NAME = @TableName

END

GO