get max date acros all tables 1

[tirf]

I have a parent table called tblUser which has UserID coulmn

and this table has more than 50 child tables (each one have userID, createdDate as their column)

I want to get the the latest created record for each table and the table the last record was created

How can I achieve this

Thanks

 

[markros]

Do you store a ModifiedDate field in each of these tables?

PluralSight Learning Library

 

[tirf]

Do you store a ModifiedDate field in each of these tables?

No, just created date

 

[markros]

Ok, we can do this using dynamic SQL.

declare @TablesCnt int, @Loop int, 
@Table sysname, @SQL nvarchar(max)
select identity(int,1,1) as Row, Table_Name into #ListOfTables from Information_Schema.Columns C1 where 
Column_Name = 'UserID' and 
exists (select 1 from Information_Schema.Columns C2
where C1.Table_Name = C2.Table_Name and 
C2.Column_Name = 'CreatedDate') order by Table_Name

create table Result (LatestDate datetime, TableName Sysname)

set @TablesCnt = @@ROWCOUNT
set @Loop = 1
while @Loop <=@TablesCnt
  begin
  select @Table = Table_Name from #ListOfTables where Row = @Loop
  set @Loop = @Loop + 1
  set @SQL = 'select max(CreatedDate) as LastDate, ' +
  quotename(@Table) + ' as Table_Name from ' + quotename(@Table)
   insert into Result execute (@SQL) 
  end

select * from Result

This is from the top of my head (not tested), you may need to adjust.

PluralSight Learning Library

 

[markros]

Here is a simpler solution:

declare @SQL varchar(max)

select @SQL = stuff((select top (50) char(13) + char(10) + 'UNION ALL ' + 
'SELECT ' + quotename(table_schema,'''') + ' as [Schema], ' + quotename(Table_Name,'''') + 
' as [TableName], max(ModifiedDate) as [LastDate] from ' + 
QUOTENAME(Table_SCHEMA) + '.' + 
quotename(Table_Name) from (select Table_Name, TABLE_SCHEMA  

 from Information_Schema.Columns C1 where 
Column_Name Like '%ID' and 
exists (select 1 from Information_Schema.Columns C2
where C1.Table_Name = C2.Table_Name and 
C2.Column_Name = 'ModifiedDate') group by TABLE_NAME, TABLE_SCHEMA) X order by Table_Name
for XML PATH(''),type).value('.','varchar(max)'),1,12,'')
--print @SQL
execute (@SQL)

I added top (50) because otherwise you may run into insufficient memory pool error.

PluralSight Learning Library

 

[Crowley16]

or, you can use a temp table to store the max dates in each table and then select max from the temp table.

if it's a one off, then you can use sp_msforeachtable and filter out the tables you don't want, or if it needs to be part of something stable, then you can use a loop or cursor...

--------------------
Procrastinate Now!

 

[tirf]

THank you all;

your solution worked out for me

 

[markros]

I tested my original suggestion - will test later the speed difference between the two (but the second doesn't list all tables). I'm using AdventureWorks database for my tests

declare @TablesCnt int, @Loop int, @Schema sysname,
@Table sysname, @SQL nvarchar(max)
if object_id('tempdb..#ListOfTables') IS NOT NULL
   drop table #ListOfTables
select identity(int,1,1) as Row, table_schema, Table_Name into #ListOfTables from Information_Schema.Columns C1 where 
Column_Name LIKE '%ID' and 
exists (select 1 from Information_Schema.Columns C2
where C1.Table_Name = C2.Table_Name and 
C2.Column_Name = 'ModifiedDate') order by Table_Name
set @TablesCnt = @@ROWCOUNT
--select * from #ListOfTables
if object_id('Result','U') IS not NULL
  drop table Result
create table Result (SchemaName sysname, TableName Sysname, LatestDate datetime)

set @Loop = 1
while @Loop <=@TablesCnt
  begin
  select @Table = Table_Name, @Schema = Table_schema from #ListOfTables where Row = @Loop
  
  set @Loop = @Loop + 1
  set @SQL = 'select ' + QUOTENAME(@schema,'''') + ' as [Schema], ' + 
  quotename(@Table,'''') + ' as Table_Name, max(ModifiedDate) as LastDate from ' + quotename(@Schema) + '.' + quotename(@Table)
   insert into Result execute (@SQL) 
   --execute (@SQL)
  end

select * from Result
go

-- Test query
declare @SQL varchar(max)

select @SQL = stuff((select top (50) char(13) + char(10) + 'UNION ALL ' + 
'SELECT ' + quotename(table_schema,'''') + ' as [Schema], ' + quotename(Table_Name,'''') + 
' as [TableName], max(ModifiedDate) as [LastDate] from ' + 
QUOTENAME(Table_SCHEMA) + '.' + 
quotename(Table_Name) from (select Table_Name, TABLE_SCHEMA  

 from Information_Schema.Columns C1 where 
Column_Name Like '%ID' and 
exists (select 1 from Information_Schema.Columns C2
where C1.Table_Name = C2.Table_Name and 
C2.Column_Name = 'ModifiedDate') group by TABLE_NAME, TABLE_SCHEMA) X order by Table_Name
for XML PATH(''),type).value('.','varchar(max)'),1,12,'')
--print @SQL
execute (@SQL)

PluralSight Learning Library

 

[markros]

You can also view both solutions here

http://wiki.lessthandot.com/index.php/Finding_Record_with_Last_Modified_date_in_all_tables

PluralSight Learning Library