Trying to create a table of table names and max values

Question

In SQL Server, we have a number of tables, all containing a field last_modified that records when a particular record was created, modified, or flagged for deletion. I want to create a table of table names and the max() value of last_modified.

I'm brute forcing it as follows:

I run a query modified from Query to list number of records in each table in a database to list tables that have rows and eliminate some internal tables.

SELECT 
    t.NAME AS TableName,
    p.[Rows]
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
WHERE 
    t.NAME NOT LIKE 'dt%' AND t.name not like '%ml%' and 
    t.NAME not in ('OrderHeaders', 'OrderHeaderExtendedText', 'OrderLIDetails', 'OrderLIDetailExtendedText', 'UserCustomerXRef', 'UserDetails', 'UserDetailExtendedText', 'UserTypeDescription') and
    p.rows <> 0 and
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.NAME, p.[Rows]
ORDER BY 
    TableName

This gives me a table like this:

Then I paste this output into Excel and create a series of queries there:

A2.value reads BidCustomerXRef. C2.Value reads ="select top 1 last_modified, '" & A2 & "' as 'Table' from " & A2 & " where deleted = 'N' order by last_modified desc" and so I get

select top 1 last_modified, 'BidCustomerXRef' as 'Table' 
from   BidCustomerXref 
where  deleted = 'N' 
order by last_modified desc`

So I copy all of those rows to my SQL query window and I get this:

What I want is a single table that looks like this:

TableName	last_modified
BidCustomerXRef	2022-06-21 21:30:07.287
Bids	2022-06-22 20:00:06.383
CustomerARDetail	2022-06-22 18:00:11.923

etc.

As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Jun 22 '22 at 21:45
If you want the results all in a single output - then add UNION ALL before the select on all but the first row. You can generate that code in SQL - no need to go to Excel to generate it either. — Jeff, Jun 22 '22 at 22:24
you can insert the result of individual query into a temp table — Squirrel, Jun 23 '22 at 02:56
You can use a cursor to iterate over a list of tables, build a dynamic SQL statement that queries the table, gets the last modified and inserts this into your results table. I can code this up if you wish. — Simon Darlow, Jun 23 '22 at 09:22

score 0 · Answer 1 · answered Jun 23 '22 at 13:56

Here is one way you can do this. I am using the system tables to generate dynamic sql instead of using a dreaded cursor or other sort of iteration going row by agonizing row.

declare @sql nvarchar(max) = ''

select @sql += 'select TableName = ''' + s.name + '.' + t.name + ''', last_modified = (select max(last_modified) from ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' where deleted = ''N'') union all '
from sys.tables t
join sys.schemas s on s.schema_id = t.schema_id
join sys.indexes i ON t.object_id = i.object_id
join sys.partitions p ON i.object_id = p.object_id and i.index_id = p.index_id
where t.NAME NOT LIKE 'dt%' 
    and t.name not like '%ml%' 
    and t.NAME not in ('OrderHeaders', 'OrderHeaderExtendedText', 'OrderLIDetails', 'OrderLIDetailExtendedText', 'UserCustomerXRef', 'UserDetails', 'UserDetailExtendedText', 'UserTypeDescription') 
    and p.rows > 0
    and i.object_id > 255
    and i.index_id <= 1
order by t.name

select @sql = left(@sql, len(@sql) - 9) --removes the last UNION ALL

select @sql

--uncomment the line below once you have evaluated that the dynamic sql is correct
--exec sp_executesql @sql

Trying to create a table of table names and max values

1 Answers1