Challenge accepted.
Find the oldest date value in database.
This is possibly the worst script I've ever written ! It does everything YOU DON"T WANT, multiple cursors, dynamic SQL, hard coded variable loops, Global Temporay table and a table scan of every table in your database.
BUT the requirement was to search every date,datetimestamp column in the database and find the oldest value.
It does have a filter that can be set, for things like columns of Date of Birth, / DOB.
/*
DATES to ignore, comma delimited list
*/
Declare @DateTimesToIgnore Varchar(256)
Declare @SmallDateTimesToIgnore varchar(256)
-- set to null to not use this
-- some applications set these dates and we want to ignore them when searching for minimums
Set @DateTimesToIgnore = '(' + char(39) + '1900-01-01 00:00:00.000' + char(39) + ',' + char(39) + '1753-01-01 00:00:00.000' + char(39) + ',' + char(39) + '1753-01-01 12:00:00.000' + char(39) + ')'
-- 1753 is not valid for smalldatetimes, so we have to remove it from the in clause for that data type
Set @SmallDateTimesToIgnore = '(' + char(39) + '1900-01-01 00:00:00.000' + char(39) + ')'
print '@DateTimesToIgnore = ' + @DateTimesToIgnore
print '@SmallDateTimesToIgnore = ' + @SmallDateTimesToIgnore
IF OBJECT_ID('tempdb..#Results', 'U') IS NOT NULL
DROP TABLE #Results
/*
-- *************** WATCH GLOBAL TEMPORARY TABLE ************************
*/
IF OBJECT_ID('tempdb..##tmp_TempResultsUnknownStructure', 'U') IS NOT NULL
DROP TABLE ##tmp_TempResultsUnknownStructure
-- CREATE TABLE #Results (SchemaName nvarchar(256),TableName nvarchar(256),XMLResults XML)
-- CREATE TABLE #Results (XMLResults XML)
CREATE TABLE #Results (SchemaName nvarchar(256),TableName nvarchar(256),ColumnName varchar(256),MinValue datetime)
SET NOCOUNT ON
DECLARE @SchemaName nvarchar(256),@TableName nvarchar(256), @ColumnName nvarchar(128), @type nvarchar(56)
Declare @SQLStatement varchar(8000)=''
Declare @SQLStatement2 varchar(8000)=''
Declare @ExecuteCounter int
Declare @SQLExecuteLoopCounter int
DECLARE @Previous_SchemaName nvarchar(256),@Previous_TableName nvarchar(256), @Previous_ColumnName nvarchar(128), @Previous_type nvarchar(56)
/*
Variables for inner cursor
*/
declare @temp_table_column_name varchar(256)
declare @column_counter int = 1
declare @schemaname_from_results varchar(256)
declare @tablename_from_results varchar(256)
DECLARE @MinValueTable TABLE (MinValue DateTime)
declare @MinValue DateTime
DECLARE cursor_tables_with_datetimes CURSOR FOR
select s.name as 'SchemaName',
tbl.name as 'Table', c.name as 'ColumnName', t.name as 'Type'
from sys.columns as c
inner join sys.tables as tbl
on tbl.object_id = c.object_id
inner join sys.types as t
on c.system_type_id = t.system_type_id
inner join sys.schemas s
on tbl.schema_id = s.schema_id
where t.name in ('DATETIME', 'DATE','DATETIME2', 'SMALLDATETIME') --, 'DATETIMEOFFSET') -- 'TIME'
--and tbl.name in ( 'archivehistory','bankruptcy','users')
and c.name not like '%DOB%'
order by s.name, tbl.name
OPEN cursor_tables_with_datetimes
FETCH NEXT FROM cursor_tables_with_datetimes
INTO @SchemaName ,@TableName , @ColumnName , @type
-- see starting @previous_{variable} used to break out and know we are changing
-- to a different set of tables with a different set of columns
-- this break means the sql statement is "complete" and ready to execute before
-- starting to build the next iteration
select @Previous_SchemaName = @SchemaName,
@Previous_TableName = @TableName,
@Previous_ColumnName = @ColumnName,
@Previous_Type = @type,
@SQLStatement = ''
WHILE @@FETCH_STATUS = 0
BEGIN
-- just a general debug statement
Print @SchemaName + '.' + @TableName
print ' ' + @columnName
-- test to "see" if we changed to a different set of table/columns,
-- if so, than execute
if @Previous_TableName <> @TableName
begin
print 'Length of @SQLStatement: ' + Convert(varchar(10),LEN(@SQLStatement))
print 'Length of @SQLStatement2: ' + Convert(varchar(10),LEN(@SQLStatement2))
If LEN(@SQLStatement2) <= 0
Set @ExecuteCounter = 1
else
Set @ExecuteCounter = 2
print '@ExecuteCounter: ' + Convert(varchar(10),@ExecuteCounter)
if @ExecuteCounter = 2
print 'Need to execute twice'
set @SQLExecuteLoopCounter = 1
While 1 <= @ExecuteCounter
Begin
Print 'in loop, @ExecuteCounter = ' + Convert(varchar(10),@ExecuteCounter)
if @SQLExecuteLoopCounter = 1
Set @SQLStatement = 'Select ' + Char(39) + @Previous_Schemaname + char(39) + ' as SchemaName, '
+ char(39) + @Previous_TableName + char(39) + ' as TableName ' + @SQLStatement +
' into ##tmp_TempResultsUnknownStructure FROM [' + @Previous_SchemaName + '].[' + @Previous_TableName + '] WITH (NOLOCK)'
else
begin
set @SQLStatement = @SQLStatement2
Set @SQLStatement = 'Select ' + Char(39) + @Previous_Schemaname + char(39) + ' as SchemaName, '
+ char(39) + @Previous_TableName + char(39) + ' as TableName ' + @SQLStatement +
' into ##tmp_TempResultsUnknownStructure FROM [' + @Previous_SchemaName + '].[' + @Previous_TableName + '] WITH (NOLOCK)'
end
print '-- *****************************'
print @SQLStatement
print '-- *****************************'
--INSERT INTO #Results
EXEC
(
@SQLStatement
)
-- now cursor through and populate table
-- begin inner cursor
print 'in inner cursor on cursor'
Set @column_counter = 1
DECLARE cursor_temp_table_structure CURSOR FOR
Select Name From tempdb.sys.columns
Where object_id=OBJECT_ID('tempdb.dbo.##tmp_TempResultsUnknownStructure')
order by column_id
OPEN cursor_temp_table_structure
FETCH NEXT FROM cursor_temp_table_structure INTO @temp_table_column_name
WHILE @@FETCH_STATUS = 0
BEGIN
-- null @MinValue on everyiteration to ensure it's not accidentally carried over
Set @MinValue = Null
delete from @MinValueTable
Print ' Temp Table Column Name = ' + @temp_table_column_name
-- first two rows are always schema name and tablename, insert those into #results
If @column_counter = 1
begin
set @schemaName_from_results = (Select top 1 schemaname from ##tmp_TempResultsUnknownStructure)
end
if @column_counter = 2
begin
set @tablename_from_results = (Select top 1 tablename from ##tmp_TempResultsUnknownStructure)
end
if @column_counter > 2
begin
INSERT @MinValueTable
exec ('SELECT TOP 1 [' + @temp_table_column_name + '] FROM ##tmp_TempResultsUnknownStructure')
Set @MinValue = ( Select top 1 MinValue from @MinValueTable)
insert into #Results values (@schemaName_from_results,@tablename_from_results,@temp_table_column_name,@MinValue)
end
-- increase loop counter
set @column_counter = @column_counter + 1
FETCH NEXT FROM cursor_temp_table_structure INTO @temp_table_column_name
END
CLOSE cursor_temp_table_structure
DEALLOCATE cursor_temp_table_structure
-- now drop the global temporary table
IF OBJECT_ID('tempdb..##tmp_TempResultsUnknownStructure', 'U') IS NOT NULL
DROP TABLE ##tmp_TempResultsUnknownStructure
-- end inner cursor
Set @SQLExecuteLoopCounter = @SQLExecuteLoopCounter + 1
Set @ExecuteCounter = @ExecuteCounter - 1
End
-- reset dynamic statements
set @SQLStatement = ''
set @SQLStatement2 = ''
end
-- check length of sql statement.
-- it's possible to exceed 8000 characters
-- in which case we take multile passes
If len(@SQLStatement) > 3700
begin
print '******************** TABLE HAS LOTS OF DATE COLUMNS ************************'
print '********* MAX LENGTH of DYNAMIC SQL = 4000 ************************'
print @Previous_TableName
end
if @DateTimesToIgnore is null
begin
if len(@SQLStatement) < 3700
set @SQLStatement = @SQLStatement + ', MIN([' + @ColumnName + ']) as [' + @columnName + ']'
else
set @SQLStatement2 = @SQLStatement2 + ', MIN([' + @ColumnName + ']) as [' + @columnName + ']'
end
else
begin
if @type = 'smalldatetime'
begin
if len(@SQLStatement) < 3700
set @SQLStatement = @SQLStatement + ', MIN(CASE WHEN [' + @ColumnName + '] in ' + @SmallDateTimesToIgnore + ' then Null else [' + @columnName + '] end) as [' + @columnName + ']'
else
set @SQLStatement2 = @SQLStatement2 + ', MIN(CASE WHEN [' + @ColumnName + '] in ' + @SmallDateTimesToIgnore + ' then Null else [' + @columnName + '] end) as [' + @columnName + ']'
end
else
begin
if len(@SQLStatement) < 3700
set @SQLStatement = @SQLStatement + ', MIN(CASE WHEN [' + @ColumnName + '] in ' + @DateTimesToIgnore + ' then Null else [' + @columnName + '] end) as [' + @columnName + ']'
else
set @SQLStatement2 = @SQLStatement2 + ', MIN(CASE WHEN [' + @ColumnName + '] in ' + @DateTimesToIgnore + ' then Null else [' + @columnName + '] end) as [' + @columnName + ']'
end
end
print '@SQLStatement = ' + @SQLStatement
-- populate previous variable, not sqlstatement not reset
select @Previous_SchemaName = @SchemaName,
@Previous_TableName = @TableName,
@Previous_ColumnName = @ColumnName,
@Previous_Type = @type
FETCH NEXT FROM cursor_tables_with_datetimes
INTO @SchemaName ,@TableName , @ColumnName , @type
END
CLOSE cursor_tables_with_datetimes
DEALLOCATE cursor_tables_with_datetimes
-- last statement not executed when breaking out of cursor, so execute it.
PRINT '---------------'
PRINT '-- Dropped out of main cursor '
PRINT '---------------'
print 'Length of @SQLStatement: ' + Convert(varchar(10),LEN(@SQLStatement))
print 'Length of @SQLStatement2: ' + Convert(varchar(10),LEN(@SQLStatement2))
If LEN(@SQLStatement2) <= 0
Set @ExecuteCounter = 1
else
Set @ExecuteCounter = 2
print '@ExecuteCounter: ' + Convert(varchar(10),@ExecuteCounter)
if @ExecuteCounter = 2
print 'Need to execute twice'
set @SQLExecuteLoopCounter = 1
While 1 <= @ExecuteCounter
Begin
Print 'in loop, @ExecuteCounter = ' + Convert(varchar(10),@ExecuteCounter)
if @SQLExecuteLoopCounter = 1
Set @SQLStatement = 'Select ' + Char(39) + @Previous_Schemaname + char(39) + ' as SchemaName, '
+ char(39) + @Previous_TableName + char(39) + ' as TableName ' + @SQLStatement +
' into ##tmp_TempResultsUnknownStructure FROM [' + @Previous_SchemaName + '].[' + @Previous_TableName + '] WITH (NOLOCK)'
else
begin
set @SQLStatement = @SQLStatement2
Set @SQLStatement = 'Select ' + Char(39) + @Previous_Schemaname + char(39) + ' as SchemaName, '
+ char(39) + @Previous_TableName + char(39) + ' as TableName ' + @SQLStatement +
' into ##tmp_TempResultsUnknownStructure FROM [' + @Previous_SchemaName + '].[' + @Previous_TableName + '] WITH (NOLOCK)'
end
print '-- *****************************'
print @SQLStatement
print '-- *****************************'
--INSERT INTO #Results
EXEC
(
@SQLStatement
)
-- now cursor through and populate table
-- begin inner cursor
print 'in inner cursor on cursor'
Set @column_counter = 1
DECLARE cursor_temp_table_structure CURSOR FOR
Select Name From tempdb.sys.columns
Where object_id=OBJECT_ID('tempdb.dbo.##tmp_TempResultsUnknownStructure')
order by column_id
OPEN cursor_temp_table_structure
FETCH NEXT FROM cursor_temp_table_structure INTO @temp_table_column_name
WHILE @@FETCH_STATUS = 0
BEGIN
-- null @MinValue on everyiteration to ensure it's not accidentally carried over
Set @MinValue = Null
delete from @MinValueTable
Print ' Temp Table Column Name = ' + @temp_table_column_name
-- first two rows are always schema name and tablename, insert those into #results
If @column_counter = 1
begin
set @schemaName_from_results = (Select top 1 schemaname from ##tmp_TempResultsUnknownStructure)
end
if @column_counter = 2
begin
set @tablename_from_results = (Select top 1 tablename from ##tmp_TempResultsUnknownStructure)
end
if @column_counter > 2
begin
INSERT @MinValueTable
exec ('SELECT TOP 1 [' + @temp_table_column_name + '] FROM ##tmp_TempResultsUnknownStructure')
Set @MinValue = ( Select top 1 MinValue from @MinValueTable)
insert into #Results values (@schemaName_from_results,@tablename_from_results,@temp_table_column_name,@MinValue)
end
-- increase loop counter
set @column_counter = @column_counter + 1
FETCH NEXT FROM cursor_temp_table_structure INTO @temp_table_column_name
END
CLOSE cursor_temp_table_structure
DEALLOCATE cursor_temp_table_structure
-- now drop the global temporary table
IF OBJECT_ID('tempdb..##tmp_TempResultsUnknownStructure', 'U') IS NOT NULL
DROP TABLE ##tmp_TempResultsUnknownStructure
-- end inner cursor
Set @SQLExecuteLoopCounter = @SQLExecuteLoopCounter + 1
Set @ExecuteCounter = @ExecuteCounter - 1
End
-- reset dynamic statements
set @SQLStatement = ''
set @SQLStatement2 = ''
-- oldest
select schemaname,tablename,columnname,minvalue
from #Results
where minvalue =
( select top 1 minvalue from #results where minvalue is not null order by minvalue )
select top 5 schemaname,tablename,columnname,minvalue
from #Results
where minvalue is not null
order by minvalue
SELECT * FROM #Results