Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Sunday, September 01, 2019

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.

It also will respect Philip Stanhope, 4th Earl of Chesterfield, The decision to use 1st January 1753 (1753-01-01) as the minimum date value for a datetime.

https://stackoverflow.com/questions/3310569/what-is-the-significance-of-1-1-1753-in-sql-server

Here it is, hate it, love it, it works.

If the format of this is not correct, check my script vault.

/*

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

 

 

Sunday, September 01, 2019 9:32:27 AM (Central Standard Time, UTC-06:00) |  | SQL Server#
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
Default Trace - Heavy load, turn it...
SQL 2008 R2 License / Cost = Open S...
Door Installation
Recent Posts
Archive
October, 2019 (1)
September, 2019 (1)
August, 2019 (1)
May, 2019 (1)
April, 2019 (1)
March, 2019 (1)
February, 2019 (1)
January, 2019 (1)
December, 2018 (1)
November, 2018 (3)
October, 2018 (1)
June, 2018 (1)
May, 2018 (3)
April, 2018 (2)
February, 2018 (3)
January, 2018 (3)
November, 2017 (1)
October, 2017 (1)
August, 2017 (1)
June, 2017 (2)
May, 2017 (2)
April, 2017 (2)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Links
Categories
Admin Login
Sign In
Blogroll