Pretty good...a couple things. The final order by Top1Date is invalid, as that isn't part of the derived table (i'm guessing a change to order by latestdate). The second, and one I missed in my script as well, was schema's besides dbo.

But the output is much more user friendly. Thanks Gav.

Here's your revised code:

/* 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 [' + in_s.table_schema + '].[' + so.name + '] table.

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

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

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

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

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

union all'

FROM syscolumns sc

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

inner join INFORMATION_SCHEMA.TABLES in_s on in_s.table_name = so.name

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.[LatestDate] 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