Gav B (6/8/2010)

---

Gaby,

The database I have tested your script against has many tables and even more date columns; 487 date columns to be exact! Seems like a good test to me...

This is a user-less test database, so I guess that combined with indexes which do exists on many date columns means I can return "top 1 dates desc" from 256 (the SQL2005 limit) different date columns, which are contained in 36 different tables, in just 10 seconds on first run, and 4 seconds on subsequent runs.

I think your script is good and it's not an area of SQL scripting I've used before..

i.e. creating dynamic SQL statements, either to run or print but I think I'm going to find it useful in future.

I've had a bash at editing yours because I thought about it in a slightly different scope.

I thought the main limitation of yours was that when you ran (EXEC) it came out pretty bad as each line was in a seperate results window.

I thought what would be better is if it came out as a single "facts" dataset.

To do this I created a derivied table stucture around all of it joining each query with a union all statement.

So please check this out and let me know what you think.

As my comments in the script explain it's not perfect yet :-):

/*SCRIPT TITLE: Find all the Latest Dates in a SQL database.

SCRPIT CREATOR: Gaby Abed (Originally) titled "A quick way to find recently entered datetime entries in tables"

which can be found online here: http://www.sqlservercentral.com/scripts/T-SQL/70201/

SCRPIT MODIFIED: Gavin Broughton on 08/06/2010

MODIFICATIONS:1) Added the whole concept of a Derived table with the 3 dynamic "fact" columns: [TableName], [DateColumnName] & [Top1Date]

2) Formatting improvements for easier readability in query windows (i.e. each query is not just 1 line)

3) Limited the output to 256 tables because you can't run the query with more than 256 tables.

- This can of course be changed if you need to output all the SQL.

4) Included Qry No.'s as a comment to each query.

KNOWN ISSUES:1) The script can not be run directly, the print output needs to be copy & pasted into a new query window to be run.

2) You need to delete the very last "union all" statement for the query to work.

- This script does not dynamically get rid of this for you at @ 08/06/2010.

*/

SET NOCOUNT ON

CREATE TABLE #FindLatestDates (rownum int identity(1,1), command varchar(8000))

INSERT INTO #FindLatestDates(command)

SELECT ' - Latest [' + sc.name + '] date in the [' + so.name + '] table.

'+'select top 1 ''' + so.name + '''[TableName]

,''' + sc.name + '''[DateColumnName]

,[' + sc.name + '][LatestDate]

from ['+ so.name + '] (nolock)

order by [' + sc.name + '] desc

union all'

FROM syscolumns sc

inner join sysobjects so on so.id = sc.id

WHERE so.type = 'U' and sc.xtype = 61

DECLARE @dtcolumns varchar(8000),

@query varchar(8000),

@dtend varchar(8000),

@ctr int,

@numrows int

set @dtcolumns = 'SELECT dt.[TableName], dt.[DateColumnName], dt.[LatestDate]

FROM ('

set @dtend = ') dt -- Derived table ** NOTE ** Delete the Union All statement just before this note before running!

ORDER BY dt.[Top1Date] DESC, dt.[TableName] ASC, dt.[DateColumnName] ASC'

select @numrows = count(*) from #FindLatestDates

set @ctr = 1

print (@dtcolumns) -- print the derived table columns at the start of the query.

while (@ctr <= @numrows)

-- NOTE: The max number of tables you can query in this way is 256 in SQL2000/2005 so this must be limited. (untested on SQL2008)

and @ctr <= 256

begin

select top 1 @query = command from #FindLatestDates

where rownum = @ctr --Edit by Gaby Abed on 08/06/2010

print '-- Qry No.' + cast(@ctr as varchar(4)) + (@query) -- Print the dynamic script to COPY & PASTE TO A NEW QUERY window.

set @ctr = @ctr + 1

end

print (@dtend)

go

--select * from #FindLatestDates order by rownum

drop table #FindLatestDates

P.S.

Perhaps a script to add indexes to date columns if they didn't have one would be quite cool?

Regards

- Gav B.