SQL Server: Tips and Tricks - 2 (2017)

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

Scenario:

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.