Login or Sign Up to become a member!
LessThanDot Sit Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot friendfeed Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Finding Record with Last Modified date in all tables

From Wiki

Jump to: navigation, search

The query below will print table and the date of last modification for each table that has field like ID (OwnerID, AddressID, etc.) and ModifiedDate. Due to the fact it creates a complex query, the result is limited to only 50 first records

tsqlLine number Off | Hide | Select all
  1. declare @SQL varchar(max)
  2.  
  3. select @SQL = stuff((select top (50) char(13) + char(10) + 'UNION ALL ' +
  4. 'SELECT ' + quotename(table_schema,'''') + ' as [Schema], ' + quotename(Table_Name,'''') +
  5. ' as [TableName], max(ModifiedDate) as [LastDate] from ' +
  6. QUOTENAME(Table_SCHEMA) + '.' +
  7. quotename(Table_Name) from (select Table_Name, TABLE_SCHEMA  
  8.  
  9.  from Information_Schema.Columns C1 where
  10. Column_Name Like '%ID' and
  11. exists (select 1 from Information_Schema.Columns C2
  12. where C1.Table_Name = C2.Table_Name and
  13. C2.Column_Name = 'ModifiedDate') group by TABLE_NAME, TABLE_SCHEMA) X order by Table_Name
  14. for XML PATH(''),type).value('.','varchar(max)'),1,12,'')
  15. --print @SQL
  16. execute (@SQL)

For the explanation of XML PATH trick please take a look at this blog Making a list and checking it twice

Alternative traditional loop solution:

tsqlLine number Off | Hide | Select all
  1. declare @TablesCnt int, @Loop int, @Schema sysname,
  2. @Table sysname, @SQL nvarchar(max)
  3. if object_id('tempdb..#ListOfTables') IS NOT NULL
  4.    drop table #ListOfTables
  5. select identity(int,1,1) as Row, table_schema, Table_Name into #ListOfTables
  6. from ( select distinct Table_Schema, Table_Name from Information_Schema.Columns C1 where
  7. Column_Name LIKE '%ID' and
  8. exists (select 1 from Information_Schema.Columns C2
  9. where C1.Table_Name = C2.Table_Name and
  10. C2.Column_Name = 'ModifiedDate')) X order by Table_Name
  11. set @TablesCnt = @@ROWCOUNT
  12. --select * from #ListOfTables
  13. if object_id('Result','U') IS not NULL
  14.   drop table Result
  15. create table Result (SchemaName sysname, TableName Sysname, LatestDate datetime)
  16.  
  17. set @Loop = 1
  18. while @Loop <=@TablesCnt
  19.   begin
  20.   select @Table = Table_Name, @Schema = Table_schema from #ListOfTables where Row = @Loop
  21.  
  22.   set @Loop = @Loop + 1
  23.   set @SQL = 'select ' + QUOTENAME(@schema,'''') + ' as [Schema], ' +
  24.   quotename(@Table,'''') + ' as Table_Name, max(ModifiedDate) as LastDate from ' + quotename(@Schema) + '.' + quotename(@Table)
  25.    insert into Result execute (@SQL)
  26.    --execute (@SQL)
  27.   end
  28.  
  29. select * from Result
  30. go

Bellow are comparison results between these two methods and the XML solution performs slightly faster (tested on AdventureWorks database)

tsqlLine number Off | Hide | Select all
  1. set statistics time off
  2. set nocount on
  3. declare @StartTime datetime2 = getdate(), @TimeElapsed int
  4.  
  5. declare @TablesCnt int, @Loop int, @Schema sysname,
  6. @Table sysname, @SQL nvarchar(max)
  7. if object_id('tempdb..#ListOfTables') IS NOT NULL
  8.    drop table #ListOfTables
  9. select identity(int,1,1) as Row, table_schema, Table_Name into #ListOfTables from
  10. (select distinct Table_Schema, Table_Name from Information_Schema.Columns C1 where
  11. Column_Name LIKE '%ID' and
  12. exists (select 1 from Information_Schema.Columns C2
  13. where C1.Table_Name = C2.Table_Name and
  14. C2.Column_Name = 'ModifiedDate')) X order by Table_Name
  15. set @TablesCnt = @@ROWCOUNT
  16. --select * from #ListOfTables
  17. if object_id('Result','U') IS not NULL
  18.   drop table Result
  19. create table Result (SchemaName sysname, TableName Sysname, LatestDate datetime)
  20.  
  21. set @Loop = 1
  22. while @Loop <=@TablesCnt
  23.   begin
  24.   select @Table = Table_Name, @Schema = Table_schema from #ListOfTables where Row = @Loop
  25.  
  26.   set @Loop = @Loop + 1
  27.   set @SQL = 'select ' + QUOTENAME(@schema,'''') + ' as [Schema], ' +
  28.   quotename(@Table,'''') + ' as Table_Name, max(ModifiedDate) as LastDate from ' + quotename(@Schema) + '.' + quotename(@Table)
  29.    insert into Result execute (@SQL)
  30.    --execute (@SQL)
  31.   end
  32.  
  33. --select * from Result
  34. select DATEDIFF(MS, @StartTime, GETDATE())
  35. go
  36.  
  37. -- Test query
  38. set nocount on
  39. declare @StartTime datetime2 = getdate(), @TimeElapsed int
  40. declare @SQL varchar(max)
  41. if object_id('Result','U') IS not NULL
  42.   drop table Result
  43. create table Result (SchemaName sysname, TableName Sysname, LatestDate datetime)
  44.  
  45. select @SQL = stuff((select  char(13) + char(10) + 'UNION ALL ' +
  46. 'SELECT ' + quotename(table_schema,'''') + ' as [Schema], ' + quotename(Table_Name,'''') +
  47. ' as [TableName], max(ModifiedDate) as [LastDate] from ' +
  48. QUOTENAME(Table_SCHEMA) + '.' +
  49. quotename(Table_Name) from (select Table_Name, TABLE_SCHEMA  
  50.  
  51.  from Information_Schema.Columns C1 where
  52. Column_Name Like '%ID' and
  53. exists (select 1 from Information_Schema.Columns C2
  54. where C1.Table_Name = C2.Table_Name and
  55. C2.Column_Name = 'ModifiedDate') group by TABLE_NAME, TABLE_SCHEMA) X order by Table_Name
  56. for XML PATH(''),type).value('.','varchar(max)'),1,12,'')
  57. --print @SQL
  58. insert into Result execute (@SQL)
  59. select DATEDIFF(MS, @StartTime, GETDATE())

771 Rating: 3.3/5 (15 votes cast)

Retrieved from "http://wiki.lessthandot.com/index.php/Finding_Record_with_Last_Modified_date_in_all_tables"

Powered by MediaWiki GNU Free Documentation License 1.2
Valid XHTML | Valid CSS