Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using SQL Server 2008, we have one database which contains 150 tables. We have introduced systimestamp DATETIME column in every tables. We do update this column with current timestamp whenever Insert/Update operation perform on the table.

How do I write a query to get the most recent (based on systimestamp column) 100 records of the database in a single result set ?

share|improve this question
    
To clarify, you want the most recent 100 records from all the tables? In a single result set? Do all the tables have the same schema? –  Dai Apr 8 '13 at 8:24
    
yes i want it into a single result set. No they do have different schema but all of them have systimestamp column. –  Piyush Patel Apr 8 '13 at 8:27
    
If they have different schema then you can't really return them all in the same resultset because of the 8KB width limit. Do you just want to return the timestamp value and nothing else? –  Dai Apr 8 '13 at 8:32
    
Do you want the last 100 records of EACH table, or the last 100 records as in ALL the tables? –  RoKa Apr 8 '13 at 8:35
    
i want last 100 records as in All the tables. @Dai, Primary key ID and table name would be fine in the result set. –  Piyush Patel Apr 8 '13 at 8:40
add comment

2 Answers

up vote 4 down vote accepted

Based on the comments above, what you will need is something like this:

create table #topdates (ID int not null, TableName nvarchar(128), systimestamp smalldatetime)
exec sp_MSforeachtable @command1 = 'insert into #topdates select top 100 ID, ''?'' as TableName, systimestamp from ? order by systimestamp desc'
select top 100 * from #topdates
order by systimestamp
drop table #topdates

This will collate the top 100 results from each table into a temporary table, and then return the top 100 rows to you.

EDIT: Based on further information provided in the comments, it was established that the ID field might actually have a different name on every table. The following SQL will list the top 100 last edited records in the entire database, across all tables, showing the Primary key value, the table name, and the systimestamp:

create table #keys (TABLE_NAME nvarchar(128), COLUMN_NAME nvarchar(128))
insert into #keys
SELECT cu.TABLE_NAME, cu.COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu 
WHERE EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
    WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
        AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME)
create table #topdates (Primary_Key nvarchar(128), TableName nvarchar(128), systimestamp smalldatetime)
declare @table nvarchar(128), @Column nvarchar(128), @sql nvarchar(max)
DECLARE cur CURSOR FOR select * from #keys 
OPEN cur
FETCH NEXT FROM cur INTO @table, @Column 
WHILE @@FETCH_STATUS = 0
BEGIN
    select @sql = 'insert into #topdates select top 100 [' + @Column + '], ''' + @table + ''', systimestamp from [' + @table + '] order by 3 desc '
    exec sp_executesql @sql 
    FETCH NEXT FROM cur INTO @table, @Column
 END
CLOSE cur
DEALLOCATE cur
select top 100 * from #topdates order by systimestamp desc, TableName, Primary_Key
drop table #keys 
drop table #topdates 

You can easily extend this to show the column name of the Primary Key column too.

share|improve this answer
    
it says ' Invalid column name 'ID'. ' –  Piyush Patel Apr 8 '13 at 9:00
    
@Piyush: if you are getting Invalid column name 'ID', it means that you have some tables that do not have a column called [ID]. Is the ID column different for every table? –  RoKa Apr 8 '13 at 9:04
    
@PiyushPatel: Based on information provided, I have extended the answer above. –  RoKa Apr 8 '13 at 9:44
add comment

I would suggest to create a view with all the tables union the systimestamp column. Then apply your select query to that view order by systimestamp with limit 100

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.