CODEWITHSUNDEEP
sqlsql-server-2005sql-manager
Gautam Arya
Gautam Arya

Reputation: 723

Know how many table is modified on particular date in database in SQL Server 2005

I have a database with 40 tables in it. So I want to find tables which are modified on the particular date like

UPDATE
please note that I need the table name and not the record id

like the names of the table whose content were modified on 20 Aug 2011

Upvotes: 0

Views: 3739

Answers (2)

AnandPhadke
AnandPhadke

Reputation: 13506

IN case of data modification,

Alter table tablename add datemodified timestamp

select count(*) from tablename where datemodified = @yourdate

Upvotes: -1

Yaroslav
Yaroslav

Reputation: 6554

Edited: @Damien_The_Unbeliever has a good point on his comment. My answer is only related to table structure, not inside data. In case you want to verify when data was changed then you need to add an updated_date column so you can query it and find when was modified.

Use catalog views, in particular sys.tables

DECLARE @auxDate datetime = '20120820 00:00:00'
SELECT tbl.name
  FROM sys.tables tbl
 WHERE modify_date = @auxDate

Modified to show table names. I had WHERE modify_date >= @auxDate but you want table names modified on a particular day, so updated the WHERE clause to show one day

Upvotes: 0

Related Questions