Chair's blog: 2020/03

2020年3月25日星期三

MSSQL 資料庫中找出資料在那個 Table

DECLARE @SearchStr NVARCHAR(200) = N'要找的文字'

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT

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')
                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 * FROM #Results

DROP TABLE #Results

參考來源：https://blog.miniasp.com/post/2010/07/12/Search-all-columns-of-all-tables-in-a-database-for-a-keyword

2020年3月25日 星期三

MSSQL 資料庫中找出資料在那個 Table

2020年3月25日星期三