복붙노트

[SQL] 2008 스튜디오 SQL 서버 관리에서 모든 테이블을 검색하여 문자열 찾기

SQL

2008 스튜디오 SQL 서버 관리에서 모든 테이블을 검색하여 문자열 찾기

2008 SQL Server 관리 Studio에서 데이터베이스의 모든 테이블에서 문자열을 검색 할 수있는 방법이 있습니까?

나는 문자열 말 존을 검색합니다. 결과는 존이 포함 된 테이블과 각각의 행을 표시해야합니다.

해결법

  1. ==============================

    1.당신이 나 같은 프로덕션 환경에 특정 제한 사항이있는 경우 임시 테이블 대신 테이블 변수를 사용 할 수 있으며, 임시 쿼리는이 프로 시저를 만들보다는.

    당신이 나 같은 프로덕션 환경에 특정 제한 사항이있는 경우 임시 테이블 대신 테이블 변수를 사용 할 수 있으며, 임시 쿼리는이 프로 시저를 만들보다는.

    물론 당신의 SQL 서버 인스턴스에 따라, 그것은 테이블 변수를 지원해야합니다.

    또한 검색 범위를 좁힐 수있는 USE 문을 추가

    USE DATABASE_NAME
DECLARE @SearchStr nvarchar(100) = 'SEARCH_TEXT'
DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL

        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END    
END

SELECT ColumnName, ColumnValue FROM @Results
  2. ==============================

    2.A는 늦었지만 잘하면 유용 비트.

    A는 늦었지만 잘하면 유용 비트.

    왜 SSMS에 통합 될 수있는 타사 도구 중 일부를 시도하지.

    나는이 기능 (SQL 2012 여유 만 매우 저렴)가 팩 스키마와 데이터를 모두 검색하고 SSMS 도구도있다 좋은 성공을 ApexSQL 검색과 (무료 100 %) 근무했습니다.

    위의 저장 프로 시저는 정말 좋은 것입니다; 그것은이 방법이 더 편리하게 내 의견에 그냥입니다. 당신이 날짜 열 또는 GUID 열 및 검색 할 경우에도, 일부 약간의 수정을 필요로 ...

  3. ==============================

    3.변경해야 SQL 서버 2012에 대한 TechDo의 답변을 업데이트하려면 : + @tablename + 'FROM' '(NOLOCK)'FROM에 '+ @tablename +'(NOLOCK) '로 +

    변경해야 SQL 서버 2012에 대한 TechDo의 답변을 업데이트하려면 : + @tablename + 'FROM' '(NOLOCK)'FROM에 '+ @tablename +'(NOLOCK) '로 +

    'WITH없이 테이블 힌트'사용되지 않는 기능이 SQL Server 버전에서 지원되지 않습니다 : 기타 현명한 다음과 같은 오류가 발생합니다.

    아래는 전체 업데이트 저장 프로 시저입니다 :

    CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN

    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE @TableName IS NOT NULL

    BEGIN
        SET @ColumnName = ''
        SET @TableName = 
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )

            IF @ColumnName IS NOT NULL

            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                    FROM ' + @TableName + 'WITH (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END    
    END

    SELECT ColumnName, ColumnValue FROM #Results
END
  4. ==============================

    4.A는 늦게 비트,하지만 당신은 쉽게 쿼리 문자열을 찾을 수 있습니다

    A는 늦게 비트,하지만 당신은 쉽게 쿼리 문자열을 찾을 수 있습니다

    DECLARE
@search_string  VARCHAR(100),
@table_name     SYSNAME,
@table_id       INT,
@column_name    SYSNAME,
@sql_string     VARCHAR(2000)

SET @search_string = 'StringtoSearch'

DECLARE tables_cur CURSOR FOR SELECT ss.name +'.'+ so.name [name], object_id FROM sys.objects so INNER JOIN sys.schemas ss ON so.schema_id = ss.schema_id WHERE  type = 'U'

OPEN tables_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id

WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id 
        AND system_type_id IN (167, 175, 231, 239)

    OPEN columns_cur

    FETCH NEXT FROM columns_cur INTO @column_name
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE [' + @column_name + '] 
            LIKE ''%' + @search_string + '%'') PRINT ''' + @table_name + ', ' + @column_name + ''''

            EXECUTE(@sql_string)

        FETCH NEXT FROM columns_cur INTO @column_name
        END

    CLOSE columns_cur

DEALLOCATE columns_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END

CLOSE tables_cur
DEALLOCATE tables_cur
  5. ==============================

    5.나는 테이블 이름의 형태로 검색 결과를 반환되는 이것에 대한 SP를 작성했습니다, 열 이름이있는 검색 키워드 문자열이 아니라 스크린 샷 다음과 같이 검색 상응하는 행 등으로 나타났다.

    나는 테이블 이름의 형태로 검색 결과를 반환되는 이것에 대한 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
  6. ==============================

    6.이것은 매우 도움이되었다. 나는 Postgre SQL 데이터베이스에이 기능을 가져 싶었다. 내가 관심을 다른 사람과 공유 할 생각. 그들에게 몇 시간이있을 것이다. 참고 :이 기능은 복사 및 Postgre 데이터베이스에서 실행할 수있는 SQL 문 목록을 만듭니다. 어쩌면 누군가가 똑똑 다음 날 Postgre 만들고 모두 하나의 함수에서 문을 실행할 수 있습니다.

    이것은 매우 도움이되었다. 나는 Postgre SQL 데이터베이스에이 기능을 가져 싶었다. 내가 관심을 다른 사람과 공유 할 생각. 그들에게 몇 시간이있을 것이다. 참고 :이 기능은 복사 및 Postgre 데이터베이스에서 실행할 수있는 SQL 문 목록을 만듭니다. 어쩌면 누군가가 똑똑 다음 날 Postgre 만들고 모두 하나의 함수에서 문을 실행할 수 있습니다.

    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;
  7. ==============================

    7.나도 하나의 테이블에서 검색 할 필요가 있었기 때문에 나는 조금을 채택했습니다 이미 여러 번이 게시물에서 언급 된 답 :

    나도 하나의 테이블에서 검색 할 필요가 있었기 때문에 나는 조금을 채택했습니다 이미 여러 번이 게시물에서 언급 된 답 :

    (또한 테이블 이름에 대한 입력을 만들어 좀 더 간단)

    ALTER PROC dbo.db_compare_SearchAllTables_sp
(
    @SearchStr nvarchar(100),
    @TableName nvarchar(256) = ''
)
AS
BEGIN
        if PARSENAME(@TableName, 2) is null
            set @TableName = 'dbo.' + QUOTENAME(@TableName, '"')

    declare @results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @ColumnName nvarchar(128) = '', @SearchStr2 nvarchar(110)
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
        IF @TableName <> ''
        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )

            IF @ColumnName IS NOT NULL
            BEGIN
                INSERT INTO @results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                    FROM ' + @TableName + ' WITH (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END    
        ELSE
    WHILE @TableName IS NOT NULL
    BEGIN
        SET @ColumnName = ''
        SET @TableName = 
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )

            IF @ColumnName IS NOT NULL
            BEGIN
                INSERT INTO @results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                    FROM ' + @TableName + ' WITH (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END    
    END

    SELECT ColumnName, ColumnValue FROM @results
END

    8. from https://stackoverflow.com/questions/15757263/find-a-string-by-searching-all-tables-in-sql-server-management-studio-2008 by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 어떻게 중복 된 항목을 삭제하려면?  (0) 2020.03.24
[SQL] 어떻게 다른 테이블에 일치하는 항목이있는 행을 선택하려면?  (0) 2020.03.24
[SQL] 트리 데이터 구조에 대한 데이터베이스 구조  (0) 2020.03.24
[SQL] "EEE MMM은 HH를 위해 dd : MM : SS의 ZZZ 일"로 날짜 형식 java.sql.Date  (0) 2020.03.23
[SQL] 어떻게 별칭 오라클의 테이블에 '로'키워드를 사용 하는가?  (0) 2020.03.23
,

티스토리툴바