/*

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

IF OBJECT_ID('tempdb..##tmp_TempResultsUnknownStructure', 'U') IS NOT NULL

DROP TABLE ##tmp_TempResultsUnknownStructure

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)

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

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

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 = ''

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