sql server 2008 tables

Hi Experts
I have a Sql Database called Master
and it has more than 200 tables.

 I would to run a query to find the names of all table been updated today.

Thanks
b001Asked:
Who is Participating?
 
mark_gaoCommented:
declare @today datetime
set @today = DATEADD (DD,DATEDIFF (dd,0,getdate()),0)
use master;
select name,* from sys.tables
where modify_date >@today
0
 
TempDBACommented:
Table been updated today. I am afraid you can't do that unless you have some manual auditing\CDC,etc is applied. If you have datetime field for all the tables where you have specified the date of data insertion you can do that, or if you have trigger that logs data with datetime somewhere, or if you have inbuilt mechanism like CDC from SQL server is in practise. Else you can't do that.
0
 
lluddenCommented:
Updated as in the table structure has been updated, or values within the table?

For the latter, add this trigger in to each table you want to track.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[LogUpdatedTimes_<TableName>] ON [dbo].<TableName> FOR INSERT, UPDATE, DELETE AS
BEGIN
      SET NOCOUNT ON

      MERGE INTO SQLTableUpdateTimes AS Target USING (
                                                                              SELECT '<TableName>' AS TableName,
                                                                                       getdate() AS CurrentUpdate
                                                                           ) AS Source ON Source.TableName = Target.TableName WHEN MATCHED THEN UPDATE
      SET Target.LastUpdate = getdate()
      WHEN NOT MATCHED THEN INSERT (Tablename, LastUpdate)
      VALUES (Source.TableName,
                  Source.CurrentUpdate);

END

and add this table:
CREATE TABLE [dbo].[SQLTableUpdateTimes](
      [TableName] [varchar](50) NOT NULL,
      [LastUpdate] [datetime] NOT NULL,
 CONSTRAINT [PK_SQLTableUpdateTimes] PRIMARY KEY CLUSTERED
(
      [TableName] ASC
)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.