Select all records created or modified after a specific date in all tables in a SQL Server db

Question

I would like a list of all new or modified records created after a specific date in all tables in a SQL Server database.

I am using SQL Server 2005 and ssms.

Is there a way to do this with one query, or some other tool?

How could I do this for one table?

Answer 1

Assuming all the tables have a ModifiedDate date column, you can then use the undocumented sp_msforeachtable proc

sp_msforeachtable 'select ''?'',count(*) 
   from ? where ModifiedDate > ''20100101'''

Just adjust the date range, I also use count(*) because I doubt you want millions of rows returned to you

If you don't have a column then you are out of luck, or if the column is named differently in every table then you need to use dynamic sql together with information_schema.columns and information_schema.tables to construct this query

Answer 2

There is nothing inherent in SQL Server where you can get that information. As @JNK indicated, you have to build that into your database design. And, you have to build the solution for each table by adding the create date as a column. Then, you can use SQL to capture the information.