how to check all tables of database for any update

Question

how to check all tables of database for any update

ghazall_Alii

SSC-Addicted

Points: 454
More actions
August 6, 2010 at 5:57 am

#157135

Hi
I need to know is it possible in sql server 2005 to inquire all the tables of database with single query at a time?
for Instance i need to retrieve the data from all the tables that have been updated in last two days.
I have date_modified column in all tables of data base that stores the date+time with each row at which respective row inserted/updated.
Is it possible that with a single query i retrieve data from all the tables that have column
date_modified =(some date between last two days)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

Well, yes & no. There really isn't any way to query all tables at once. But, you could set up a query where you UNION a query against each table, something like this:

SELECT 'table1' as TableName

,count(*) as RowsAffected

FROM schema.Table1

WHERE UpdateDate > DATEADD('dd',-1,getdate())

UNION

SELECT 'table2' as TableName

,count(*) as RowsAffected

FROM schema.Table2

WHERE UpdateDate > DATEADD('dd',-1,getdate())

... etc

But you'll have to build it.

You could try sp_msforeachtable, it's an undocumented procedure that basically creates a cursor, but you'll get multiple result sets instead of one using that or any other cursor-based approach (unless you also load the cursor into a temp table...)

Any way you go about it, you're looking at a lot of work.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ghazall_Alii SSC-Addicted Points: 454 More actions · Answer 2

@Grant Fritchey Thank you so much for ur response actually i want to maintain a log of each table of the database to track any updates ,anyways again i appericiate ur concern your answer cleared so many things in my mind.

sumit.agrawal SSCommitted Points: 1616 More actions · Answer 3

Create 1 table that will contain 1 row for each table in the database and a datetime column that will be having last_modified_date. This table then will be updated by triggers... (triggers must be created on all the tables of the database)...

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 4

Certainly you can automate Grant's UNION query approach by using FOR XML PATH. Perhaps you can adapt the script below to suit your requirements.

DECLARE @SQL VARCHAR(MAX)

SELECT @SQL = STUFF((SELECT 'UNION ALL SELECT ' + QUOTENAME(TABLE_NAME, '''') + ' AS TableName, COUNT(*) AS RowsModified FROM ' + QUOTENAME(TABLE_NAME, ']') + ' WHERE date_modified > DATEADD(DAY, -2, CURRENT_TIMESTAMP)' + CHAR(10)

FROM information_schema.tables WHERE TABLE_TYPE = 'BASE TABLE' FOR XML PATH(''), TYPE).value('.[1]', 'varchar(MAX)'),1, 10, '')

PRINT(@SQL)

--EXEC(@SQL)

WayneS SSC Guru Points: 95460 More actions · Answer 5

IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL DROP TABLE #TestTable;

CREATE TABLE #TestTable (TableName sysname, Qty int);

execute sp_msforeachtable 'insert into #TestTable select ''?'', count(*) from ? where date_modified >= DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP)-2, 0)';

SELECT TableName, Qty

FROM #TestTable;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ghazall_Alii SSC-Addicted Points: 454 More actions · Answer 6

Thank you so much for all your replies u are great ppl, i will try the approaches u have suggested.