May 5, 2010 at 4:16 pm
Comments posted to this topic are about the item A quick way to find recently entered datetime entries in tables
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
June 8, 2010 at 5:08 am
Gaby - I have a question about your script.
What is the purpose of of the line which says:
[font="System"]delete #findAccessTimes where rownum = @ctr[/font]
It seems to work just fine with or without it?
- Gav B
June 8, 2010 at 7:45 am
Gav B (6/8/2010)
Gaby - I have a question about your script.What is the purpose of of the line which says:
[font="System"]delete #findAccessTimes where rownum = @ctr[/font]
It seems to work just fine with or without it?
- Gav B
Oops, my bad...buggy code. As there is never a guarantee on which row is selected without an expressed WHERE clause, I should have caught that. Error fixed and submitted for revision in main body of article.
Try this:
set nocount on
create table #findAccessTimes (rownum int identity(1,1), command varchar(8000))
insert into #findAccessTimes(command)
select 'select top 1 ''' + so.name + ''' [TableName], [' + sc.name + '] from ['
+ so.name + '] (nolock) order by [' + sc.name + '] desc' from syscolumns sc
inner join sysobjects so on so.id = sc.id
where so.type = 'U' and sc.xtype = 61
declare @query varchar(8000),
@ctr int,
@numrows int
select @numrows = count(*) from #findAccessTimes
set @ctr = 1
while (@ctr <= @numrows)
begin
select @query = command from #findAccessTimes where rownum = @ctr -- Edit here
print(@query) -- Verify with this print to make sure you like the queries
-- exec(@query) -- that are generated before actually running them.
delete #findAccessTimes where rownum = @ctr
set @ctr = @ctr + 1
end
go
drop table #findAccessTimes
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
June 8, 2010 at 8:08 am
Gaby,
I think you just found a bug I hadn't actually spotted! So glad I was of some help in a round-about way.
I am not sure why that section is there though, or how it works... That's the explanation I was looking for...?
[Edit] I get it now... It needed to be in the WHERE, and you don't need a DELETE clause at all. 😎 [/Edit]
I have made some, what I think, are improvements to make the whole thing even more useful but I wouldn't want to post that (crediting you of course) without understanding everything about how this works.
Regards,
Gav B.
June 8, 2010 at 9:01 am
Gav B (6/8/2010)
Gaby,I think you just found a bug I hadn't actually spotted! So glad I was of some help in a round-about way.
I am not sure why that section is there though, and how it works... That's the explanation I was looking for...?
I have made some, what I think, are improvements to make the whole thing even more useful but I wouldn't want to post that (crediting you of course) without understanding everything about how this works.
Regards,
Gav B.
I welcome the edits (my TSQL is still woefully inadequate). But also, as I said, the two biggest limitations in this are guaranteeing the SORT BY section runs quickly (i.e. column is indexed) and that dates are stored in a datetime (or now that I think of it, the new SQL 2008 Datetime datatypes) format. One purpose of the print statements is also help to view execution plans and see what will and will not be used in terms of indexes.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
June 8, 2010 at 10:51 am
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.
June 8, 2010 at 11:24 am
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.
Thanks Gav for the edit. Regarding the index creation, I would recommend against automating it in any way, especially against unfamiliar tables. It's probably a dangerous thing to do in a production environment and it could also mess up the state of your development and staging tables. I'd still go the PRINT route and verify if it is worth creating the index, especially on large databases.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
June 9, 2010 at 3:25 am
Gaby,
Points noted. (Indexes haven't been my job / of my concern yet)
Did you try my edit out?
If so what did you think / can you think of any further improvements?
- Gav
June 9, 2010 at 8:16 am
Gav B (6/9/2010)
Gaby,Points noted. (Indexes haven't been my job / of my concern yet)
Did you try my edit out?
If so what did you think / can you think of any further improvements?
- Gav
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
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
June 9, 2010 at 9:09 am
Gaby,
Good stuff, thanks.
I like to be really description and accurate with my column titles and I kept changing my mind what to call the date column.
Finally settled on LatestDate since the script is DESC (this obviously could easily be changed to ASC & the column name to EarliestDate)
That final order by is very optional - ordering by the TableName is very useful as well.
Can anybody else out there solve:
a) the 256 table limit problem?
b) the final union all statement you need to delete issue?
c) if yes to a&b then make it an EXEC Table Function (with suggestions for possible parameters?)
...and maybe d) an extra column, so you get EarliestDate as well as the LastestDate - That would be 😎
This might all seem a bit pointless 😉 if you work with a database with hardly any dates, but trust me it's very useful if you do have a lot of dates to lookup (in my case it's due to a badly designed database I inherited & report from)
- Gav B.
May 19, 2016 at 6:50 am
Thanks for the script.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply