SQL Server数据库全库搜索字符串

[sql]代码库

DECLARE @keyWord NVARCHAR(128) = 'A'
 
DECLARE @Results AS TABLE (
    ColumnName NVARCHAR(370),
    ColumnValue NVARCHAR(3630)
    )  
  
SET NOCOUNT ON 
  
DECLARE @TableName NVARCHAR(256),
    @TableName2 NVARCHAR(258),
    @ColumnName NVARCHAR(128),
    @ColumnName2 NVARCHAR(130),
    @SearchStr2 NVARCHAR(110)
  
SET @TableName = '' 
  
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
                )  
        SET @TableName2 = REPLACE(@TableName, '''', '''''')
  
        IF @ColumnName IS NOT NULL
        BEGIN
            SET @ColumnName2 = REPLACE(@ColumnName, '''', '''''')
  
            INSERT INTO @Results
            EXEC (
                    'IF EXISTS(SELECT TOP 1 1 FROM ' + @TableName + ' (NOLOCK) WHERE ' + @ColumnName + ' LIKE ''%' + @keyWord + '%'') SELECT DISTINCT ''' + @TableName2 + '.' + @ColumnName2 + ''', LEFT(' + @ColumnName + ', 3630) 
                                    FROM ' + @TableName + ' (NOLOCK) WHERE ' + @ColumnName + ' LIKE ''%' + @keyWord + '%'''
                    )
        END
    END
END 
  
SELECT ColumnName,
    DATA_TYPE + CASE
        WHEN [CHARACTER_MAXIMUM_LENGTH] IS NULL
            THEN CASE
                    WHEN [DATA_TYPE] = 'decimal'
                        THEN '(' + CAST([NUMERIC_PRECISION] AS VARCHAR) + ',' + CAST([NUMERIC_SCALE] AS VARCHAR) + ')'
                    ELSE ''
                    END
        ELSE '(' + CASE
                WHEN [CHARACTER_MAXIMUM_LENGTH] = - 1
                    THEN 'MAX'
                ELSE CAST([CHARACTER_MAXIMUM_LENGTH] AS VARCHAR)
                END + ')'
        END [ColumnType],
    ColumnValue
FROM @Results R
INNER JOIN INFORMATION_SCHEMA.COLUMNS C
    ON QUOTENAME(C.[TABLE_SCHEMA]) + '.' + QUOTENAME(C.[TABLE_NAME]) + '.' + QUOTENAME(C.[COLUMN_NAME]) = ColumnName
ORDER BY ColumnName