SQL

How to Search in all Columns for all tables in a database for Date Value in SQL Server - SQL Server

USE YourDBName
GO

--Provide The Date for Search Condition to @DateCondition Variable
DECLARE @DateCondition VARCHAR(50)

--Provide the data criteria here
--Sample, you can use = such as =1980-01-01
--or < or <=  such as '<=1980-01-01'
--or > or >=  such as '<=1980-01-01'
SET @DateCondition = '1980-01-01'
DECLARE @DatabaseName VARCHAR(100)
DECLARE @SchemaName VARCHAR(100)
DECLARE @TableName VARCHAR(100)
DECLARE @ColumnName VARCHAR(100)
DECLARE @FullyQualifiedTableName VARCHAR(500)
DECLARE @DateConditionFormatted VARCHAR(100)

SELECT @DateConditionFormatted = CASE 
        WHEN Substring(@DateCondition, 1, 2) = '<='
            THEN Replace(@DateCondition, '<=', '<=''') + ''''
        WHEN Substring(@DateCondition, 1, 2) = '>='
            THEN Replace(@DateCondition, '>=', '>=''') + ''''
        WHEN Substring(@DateCondition, 1, 1) = '>'
            AND Substring(@DateCondition, 1, 2) != '='
            THEN Replace(@DateCondition, '>', '>''') + ''''
        WHEN Substring(@DateCondition, 1, 1) = '<'
            AND Substring(@DateCondition, 1, 2) != '='
            THEN Replace(@DateCondition, '<', '<''') + ''''
        WHEN Substring(@DateCondition, 1, 1) = '='
            THEN Replace(@DateCondition, '=', '=''') + ''''
        END

--Print @DateConditionFormatted
--Create Temp Table to Save Results
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results

CREATE TABLE #Results (
    DatabaseName VARCHAR(100)
    ,SchemaName VARCHAR(100)
    ,TableName VARCHAR(100)
    ,ColumnName VARCHAR(100)
    ,SearchedCondition VARCHAR(50)
    ,TotalTableRowCount INT
    ,FoundRowCount INT
    )

DECLARE Cur CURSOR
FOR
SELECT C.Table_CataLog
    ,C.Table_Schema
    ,C.Table_Name
    ,C.Column_Name
    ,'[' + C.Table_CataLog + ']' + '.[' + C.Table_Schema + '].' + '[' + C.Table_Name + ']' AS FullQualifiedTableName
FROM information_schema.Columns C
INNER JOIN information_Schema.Tables T ON C.Table_Name = T.Table_Name
    AND T.Table_Type = 'BASE TABLE'
    AND C.Data_Type IN (
        'date'
        ,'datetime'
        ,'datetime2'
        )

OPEN Cur

FETCH NEXT
FROM Cur
INTO @DatabaseName
    ,@SchemaName
    ,@TableName
    ,@ColumnName
    ,@FullyQualifiedTableName

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL VARCHAR(MAX) = NULL

    SET @SQL = ' Select ''' + @DatabaseName + ''' AS DatabaseName, ''' + @SchemaName + ''' AS TableName,
      ''' + @TableName + ''' AS SchemaName,
      ''' + @ColumnName + ''' AS ColumnName,
      ''' + @DateCondition + ''',(Select count(*) from ' + @FullyQualifiedTableName + ' 

with (nolock)) 
      AS TotalTableRowCount,
      count(*) as SearchRowCount from ' + @FullyQualifiedTableName + ' with (nolock)

 Where ' + @ColumnName + ' ' + @DateConditionFormatted


    --Print @SQL
    INSERT INTO #Results
    EXEC (@SQL)

    FETCH NEXT
    FROM Cur
    INTO @DatabaseName
        ,@SchemaName
        ,@TableName
        ,@ColumnName
        ,@FullyQualifiedTableName
END

CLOSE Cur

DEALLOCATE Cur

SELECT *
FROM #Results
WHERE FoundRowCount <> 0
    --drop table #Results