How to display last 100 rows from all tables across database

Question

Let's say I have a database with N tables (30-50) with different data. All tables have column "CreatedDate"

So, I want to select last 100 records from database based on this date for all tables for log/audit purposes.

One way is to do union all on all tables manually. Or use stored procedure along with sys.tables

But I thought there's a better way to see a convenient list of all records inserted in sql server.

Maybe there's a built-in or 3rd party tool for it as well.

I'd guess this is a problem caused by a less good table design. Why do you have that many tables with that column in? — jarlh, Dec 17 '15 at 12:23
@jarlh . . . Typically, I have a column called `CreatedOn` in all tables in my databases. I don't consider my designs to be "less good". — Gordon Linoff, Dec 17 '15 at 12:29
But to UNION tables, they have to be similar, at least partly. How do you know which columns to UNION? — jarlh, Dec 17 '15 at 12:33

score 1 · Accepted Answer · answered Dec 17 '15 at 13:12

I guess thos top 100 rowas may be from just one of the tables hence the TOP 100 for each table. But with below, I believe you just need to write the function "FunctionToGetAllColumnsForTableIntoOneBigOne()" whcich you should be able to do with the other link I supplied.....

DECLARE @dt DateTime = GetDate();
DECLARE @sqlCommand NVARCHAR(MAX);
DECLARE @tmpsqlCommand NVARCHAR(100);
DECLARE curTbls CURSOR FOR SELECT 'SELECT TOP 100 CreatedDate, ' + FunctionToGetAllColumnsForTableIntoOneBigOne(name) as colMain + ' FROM ' + name + ' UNION ALL ' FROM Sys.tables;
OPEN curTbls

FETCH NEXT FROM curTbls 
INTO @sqlCommand;

WHILE @@FETCH_STATUS = 0
BEGIN
  FETCH NEXT FROM curTbls 
  INTO @tmpsqlCommand;

  SET @sqlCommand = @sqlCommand + @tmpsqlCommand;
END;
CLOSE curTbls;
DEALLOCATE curTbls;

SET @sqlCommand = @sqlCommand + ' SELECT GetDate(),'

SET @sqlCommand = 'SELECT TOP 100 * FROM ( '+ @sqlCommand + ') sub_q ORDER BY CreatedDate DESC'

--SELECT @sqlCommand
EXECUTE sp_executesql @sqlCommand

Good luck - I do hope this is of some assistance.

score 0 · Answer 2 · answered Dec 17 '15 at 12:42

You can use dynamic SQL ... hope this helps

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'

SELECT TOP (100) * 
   FROM ' + QUOTENAME(SCHEMA_NAME([schema_id]))
  + '.' + QUOTENAME(name) 
  + ' 
   ORDER BY CreatedDate DESC'';'
FROM sys.tables AS t;

PRINT @sql;

EXEC sp_executesql @sql;

OR

Wrap around dynamic SQL and this should help you

SELECT 'Select Top 100 * From ' + SCHEMA_NAME(schema_id) + '.' + name +'
 ORDER BY CreatedOn DESC'
FROM sys.objects 
WHERE TYPE = 'U'

OR

EXEC sp_MSforeachtable 'select top(100) * from ?'

Note that OP wants a total of 100 rows (the most recent ones), found in 30 - 50 different tables. — jarlh, Dec 17 '15 at 13:05

score -1 · Answer 3 · answered Dec 17 '15 at 12:31

-1

Something like this...... You'll have to add your date condition in...

DECLARE @dt DateTime = GetDate()
DECLARE @sqlCommand NVARCHAR(100);
DECLARE curTbls CURSOR FOR SELECT 'SELECT TOP 100 * FROM ' + name 
                            FROM Sys.tables;
OPEN curTbls

FETCH NEXT FROM curTbls 
INTO @sqlCommand;

--SELECT @sqlCommand;
EXECUTE sp_executesql @sqlCommand

WHILE @@FETCH_STATUS = 0
BEGIN
  FETCH NEXT FROM curTbls 
  INTO @sqlCommand;

  --SELECT @sqlCommand;
  EXECUTE sp_executesql @sqlCommand
END;
CLOSE curTbls;
DEALLOCATE curTbls;

answered Dec 17 '15 at 12:31

AntDC

1,807
14
23

Thanks for the script. But I want to select top 100 records from all tables. Not from each table. Basically latest 100 records in the database. – user194076 Dec 17 '15 at 12:36
Oh I see. What you could do is concatenate all fields appart from the datetime one of each table into a large NVARCHAR. Careful of NULLS. UNION ALL each statement together and wrap it all in a sub select. Then SELCT top 100 FROM sub select ORDER BY DateColum DESC; A combination of what I originally supplied and this http://stackoverflow.com/questions/1574407/how-to-concatenate-n-columns-into-one should get you there.. Good luck – AntDC Dec 17 '15 at 12:59
Not sure why I just got a down vote. I think this is a decent foundation to contruct a solution from. – AntDC Apr 27 '16 at 14:47

How to display last 100 rows from all tables across database

3 Answers3