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.