USE YourDBName
GO
DECLARE @DateCondition VARCHAR(50)
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
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
DROP TABLE
CREATE TABLE
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
INSERT INTO
EXEC (@SQL)
FETCH NEXT
FROM Cur
INTO @DatabaseName
,@SchemaName
,@TableName
,@ColumnName
,@FullyQualifiedTableName
END
CLOSE Cur
DEALLOCATE Cur
SELECT *
FROM
WHERE FoundRowCount <> 0