0

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

  • How many tables were modified on 20 Aug 2011 in my database?

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

Gautam Arya
  • 723
  • 14
  • 40

2 Answers2

0

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

Yaroslav
  • 6,476
  • 10
  • 48
  • 89
  • This will give you all the tables modified after specific date.And not for particular date. – AnandPhadke Aug 07 '12 at 06:34
  • AFAIK, sys.tables is updated whenever, table column is added or changed. Are you sure it is updated when table data is changed? – danish Aug 07 '12 at 06:58
  • @Damien_The_Unbeliever asked already about it. I answered thinking on table structure. – Yaroslav Aug 07 '12 at 07:07
-1

IN case of data modification,

Alter table tablename add datemodified timestamp

select count(*) from tablename where datemodified = @yourdate
AnandPhadke
  • 13,160
  • 5
  • 26
  • 33