Welcome To TechBrothersIT: How to Search in all Columns for all tables in a database for Date Value in SQL Server

Scenario: Download Script

You are working as SQL Server / TSQL developer and you need to write some scripts for data validation. You got this requirement in which you need to find all columns in all tables in a database for if they qualify with date value you have provided in your criteria.

We will be only searching in columns which are date,datetime or datetime2 data type
We need to get total Record Count in a table and also Row Count which meets our criteria

E.g I want to find all the tables with column names if they have data where date columns value is ='1980-01-01'

Solution:

We can use cursor to find all the tables and columns ( Date, datetime ,datetime2 type) if our date value exists in those columns.
We will be looping through all the tables with column of date, datetime and datetime2 type. This can be expensive. If your database is small and have few tables , you might want to run it right away. If you have thousands of tables with a lot of data, you might want to run on weekend or when have very less user activity.

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

I ran above code my database and here are my results.

How to search Date value in all columns of all tables in a database in SQL Server

Check out our other posts on Data Analysis / Data Validation / Data Cleansing

Welcome To TechBrothersIT

Label

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

Scenario: Download Script

Solution:

2 comments: