SQL Server数据库全库搜索日期时间

[sql]代码库

DECLARE @keyDateTimeValue DATETIME = '1/1/2015 12:00:00 AM'
 
DECLARE @Results AS TABLE (
    ColumnName NVARCHAR(370),
    ColumnValue NVARCHAR(3630)
    )
  
SET NOCOUNT ON
  
DECLARE @TableName NVARCHAR(256),
    @TableName2 NVARCHAR(258),
    @DateTimeColumnName NVARCHAR(128),
    @DateTimeColumnName2 NVARCHAR(130),
    @DateColumnName NVARCHAR(128),
    @DateColumnName2 NVARCHAR(130),
    @TimeColumnName NVARCHAR(128),
    @TimeColumnName2 NVARCHAR(130),
    @SearchStr2 NVARCHAR(110)
  
SET @TableName = ''
  
WHILE @TableName IS NOT NULL
BEGIN
    SET @DateTimeColumnName = ''
    SET @DateColumnName = ''
    SET @TimeColumnName = ''
    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 (
            @DateTimeColumnName IS NOT NULL
            OR @DateColumnName IS NOT NULL
            OR @TimeColumnName IS NOT NULL
            )
    BEGIN
        SET @DateTimeColumnName = (
                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 (
                        'datetime',
                        'datetime2',
                        'smalldatetime',
                        'datetimeoffset',
                        'timestamp'
                        )
                    AND QUOTENAME(COLUMN_NAME) > @DateTimeColumnName
                )
        SET @DateColumnName = (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
                    AND TABLE_NAME = PARSENAME(@TableName, 1)
                    AND DATA_TYPE = 'date'
                    AND QUOTENAME(COLUMN_NAME) > @DateColumnName
                )
        SET @TimeColumnName = (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
                    AND TABLE_NAME = PARSENAME(@TableName, 1)
                    AND DATA_TYPE = 'time'
                    AND QUOTENAME(COLUMN_NAME) > @TimeColumnName
                )
        SET @TableName2 = REPLACE(@TableName, '''', '''''')
  
        IF @DateTimeColumnName IS NOT NULL
        BEGIN
            SET @DateTimeColumnName2 = REPLACE(@DateTimeColumnName, '''', '''''')
  
            DECLARE @dt1 AS NVARCHAR(30) = CONVERT(NVARCHAR, @keyDateTimeValue, 121)
  
            INSERT INTO @Results
            EXEC (
                    'IF EXISTS(SELECT TOP 1 1 FROM ' + @TableName + ' (NOLOCK) WHERE ' + @DateTimeColumnName + ' = ''' + @dt1 + ''') SELECT DISTINCT ''' + @TableName2 + '.' + @DateTimeColumnName2 + ''', LEFT(' + @DateTimeColumnName + ', 3630) 
                                        FROM ' + @TableName + ' (NOLOCK) WHERE ' + @DateTimeColumnName + ' = ''' + @dt1 + ''''
                    )
        END
  
        IF @DateColumnName IS NOT NULL
        BEGIN
            SET @DateColumnName2 = REPLACE(@DateColumnName, '''', '''''')
  
            DECLARE @dt2 AS NVARCHAR(20) = CONVERT(NVARCHAR, @keyDateTimeValue, 111)
  
            INSERT INTO @Results
            EXEC (
                    'IF EXISTS(SELECT TOP 1 1 FROM ' + @TableName + ' (NOLOCK) WHERE ' + @DateColumnName + ' = ''' + @dt2 + ''') SELECT DISTINCT ''' + @TableName2 + '.' + @DateColumnName2 + ''', LEFT(' + @DateColumnName + ', 3630) 
                                        FROM ' + @TableName + ' (NOLOCK) WHERE ' + @DateColumnName + ' = ''' + @dt2 + ''''
                    )
        END
  
        IF @TimeColumnName IS NOT NULL
        BEGIN
            SET @TimeColumnName2 = REPLACE(@TimeColumnName, '''', '''''')
  
            DECLARE @dt3 AS NVARCHAR(20) = CONVERT(NVARCHAR, @keyDateTimeValue, 114)
  
            INSERT INTO @Results
            EXEC (
                    'IF EXISTS(SELECT TOP 1 1 FROM ' + @TableName + ' (NOLOCK) WHERE ' + @TimeColumnName + ' = ''' + @dt3 + ''') SELECT DISTINCT ''' + @TableName2 + '.' + @TimeColumnName2 + ''', LEFT(' + @TimeColumnName + ', 3630) 
                                        FROM ' + @TableName + ' (NOLOCK) WHERE ' + @TimeColumnName + ' = ''' + @dt3 + ''''
                    )
        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