SQL Server数据库全库搜索数字

[sql]代码库

DECLARE @keyValue NVARCHAR(12) = '1'
 
DECLARE @Results AS TABLE (
    ColumnName NVARCHAR(370),
    ColumnValue NVARCHAR(3630)
    )
  
SET NOCOUNT ON
  
DECLARE @TableName NVARCHAR(256),
    @TableName2 NVARCHAR(258),
    @NumericColumnName NVARCHAR(128),
    @NumericColumnName2 NVARCHAR(130),
    @SearchStr2 NVARCHAR(110)
  
SET @TableName = ''
  
WHILE @TableName IS NOT NULL
BEGIN
    SET @NumericColumnName = ''
    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 (@NumericColumnName IS NOT NULL)
    BEGIN
        SET @NumericColumnName = (
                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 (
                        'tinyint',
                        'smallint',
                        'int',
                        'bigint',
                        'real',
                        'float',
                        'decimal',
                        'numeric',
                        'money',
                        'smallmoney'
                        )
                    AND QUOTENAME(COLUMN_NAME) > @NumericColumnName
                )
        SET @TableName2 = REPLACE(@TableName, '''', '''''')
  
        IF @NumericColumnName IS NOT NULL
        BEGIN
            SET @NumericColumnName2 = REPLACE(@NumericColumnName, '''', '''''')
  
            INSERT INTO @Results
            EXEC (
                    'IF EXISTS(SELECT TOP 1 1 FROM ' + @TableName + ' (NOLOCK) WHERE ' + @NumericColumnName + ' = ' + '1' + ') SELECT DISTINCT ''' + @TableName2 + '.' + @NumericColumnName2 + ''', LEFT(' + @NumericColumnName + ', 3630) 
                                        FROM ' + @TableName + ' (NOLOCK) WHERE ' + @NumericColumnName + ' = ' + @keyValue
                    )
        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