[sql] SQL Server Management Studio 2008에서 모든 테이블을 검색하여 문자열 찾기

검색 결과를 테이블 이름, 검색 키워드 문자열을 찾은 열 이름 및 아래 스크린 샷과 같이 해당 행을 검색하는 SP를 작성했습니다.

이것은 가장 효율적인 솔루션은 아니지만 필요에 따라 언제든지 수정하고 사용할 수 있습니다.

IF OBJECT_ID('sp_KeywordSearch', 'P') IS NOT NULL
    DROP PROC sp_KeywordSearch
GO

CREATE PROCEDURE sp_KeywordSearch @KeyWord NVARCHAR(100)
AS
BEGIN
    DECLARE @Result TABLE
        (TableName NVARCHAR(300),
         ColumnName NVARCHAR(MAX))

    DECLARE @Sql NVARCHAR(MAX),
        @TableName NVARCHAR(300),
        @ColumnName NVARCHAR(300),
        @Count INT

    DECLARE @tableCursor CURSOR

    SET @tableCursor = CURSOR LOCAL SCROLL FOR
    SELECT  N'SELECT @Count = COUNT(1) FROM [dbo].[' + T.TABLE_NAME + '] WITH (NOLOCK) WHERE CAST([' + C.COLUMN_NAME +
            '] AS NVARCHAR(MAX)) LIKE ''%' + @KeyWord + N'%''',
            T.TABLE_NAME,
            C.COLUMN_NAME
    FROM    INFORMATION_SCHEMA.TABLES AS T WITH (NOLOCK)
    INNER JOIN INFORMATION_SCHEMA.COLUMNS AS C WITH (NOLOCK)
    ON      T.TABLE_SCHEMA = C.TABLE_SCHEMA AND
            T.TABLE_NAME = C.TABLE_NAME
    WHERE   T.TABLE_TYPE = 'BASE TABLE' AND
            C.TABLE_SCHEMA = 'dbo' AND
            C.DATA_TYPE NOT IN ('image', 'timestamp')

    OPEN @tableCursor
    FETCH NEXT FROM @tableCursor INTO @Sql, @TableName, @ColumnName

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @Count = 0

        EXEC sys.sp_executesql
            @Sql,
            N'@Count INT OUTPUT',
            @Count OUTPUT

        IF @Count > 0
        BEGIN
            INSERT  INTO @Result
                    (TableName, ColumnName)
            VALUES  (@TableName, @ColumnName)
        END

        FETCH NEXT FROM @tableCursor INTO @Sql, @TableName, @ColumnName
    END

    CLOSE @tableCursor
    DEALLOCATE @tableCursor

    SET @tableCursor = CURSOR LOCAL SCROLL FOR
    SELECT  SUBSTRING(TB.Sql, 1, LEN(TB.Sql) - 3) AS Sql, TB.TableName, SUBSTRING(TB.Columns, 1, LEN(TB.Columns) - 1) AS Columns
    FROM    (SELECT R.TableName, (SELECT R2.ColumnName + ', ' FROM @Result AS R2 WHERE R.TableName = R2.TableName FOR XML PATH('')) AS Columns,
                    'SELECT * FROM ' + R.TableName + ' WITH (NOLOCK) WHERE ' +
                    (SELECT 'CAST(' + R2.ColumnName + ' AS NVARCHAR(MAX)) LIKE ''%' + @KeyWord + '%'' OR '
                     FROM   @Result AS R2
                     WHERE  R.TableName = R2.TableName
                    FOR
                     XML PATH('')) AS Sql
             FROM   @Result AS R
             GROUP BY R.TableName) TB
    ORDER BY TB.Sql

    OPEN @tableCursor
    FETCH NEXT FROM @tableCursor INTO @Sql, @TableName, @ColumnName

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        PRINT @Sql
        SELECT  @TableName AS [Table],
                @ColumnName AS Columns
        EXEC(@Sql)

        FETCH NEXT FROM @tableCursor INTO @Sql, @TableName, @ColumnName
    END

    CLOSE @tableCursor
    DEALLOCATE @tableCursor

END

답변

DECLARE @search VARCHAR(100), @table SYSNAME, @column SYSNAME

DECLARE curTabCol CURSOR FOR
    SELECT c.TABLE_SCHEMA + '.' + c.TABLE_NAME, c.COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS c
    JOIN INFORMATION_SCHEMA.TABLES t
      ON t.TABLE_NAME=c.TABLE_NAME AND t.TABLE_TYPE='BASE TABLE' -- avoid views
    WHERE c.DATA_TYPE IN ('varchar','nvarchar') -- searching only in these column types
    --AND c.COLUMN_NAME IN ('NAME','DESCRIPTION') -- searching only in these column names

SET @search='john'

OPEN curTabCol
FETCH NEXT FROM curTabCol INTO @table, @column

WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXECUTE('IF EXISTS
             (SELECT * FROM ' + @table + ' WHERE ' + @column + ' = ''' + @search + ''')
             PRINT ''' + @table + '.' + @column + '''')
    FETCH NEXT FROM curTabCol INTO @table, @column
END

CLOSE curTabCol
DEALLOCATE curTabCol

CREATE OR REPLACE FUNCTION SearchAllTables(_search text) RETURNS TABLE( txt text ) as $funct$
    DECLARE __COUNT int;
    __SQL text;
BEGIN
    EXECUTE 'SELECT COUNT(0) FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE    DATA_TYPE = ''text''
                    AND          table_schema = ''public'' ' INTO __COUNT;

    RETURN QUERY
        SELECT CASE WHEN ROW_NUMBER() OVER (ORDER BY table_name) < __COUNT THEN
            'SELECT ''' || table_name ||'.'|| column_name || ''' AS tbl, "'  || column_name || '" AS col FROM "public"."' || "table_name" || '" WHERE "'|| "column_name" || '" ILIKE ''%' || _search  || '%'' UNION ALL'
            ELSE
            'SELECT ''' || table_name ||'.'|| column_name || ''' AS tbl, "'  || column_name || '" AS col FROM "public"."' || "table_name" || '" WHERE "'|| "column_name" || '" ILIKE ''%' || _search  || '%'''
        END AS txt

                    FROM     INFORMATION_SCHEMA.COLUMNS
                    WHERE    DATA_TYPE = 'text'
                    AND          table_schema = 'public';
END
$funct$ LANGUAGE plpgsql;