Query to identify the number of revisions made to a table

Question

Is there any query which can return me the number of revisions made to the structure of a database table?

Secondly, how can I determine the number of pages (in terms of size) present in mdf or ldf files?

Answer 1

I think you would need to create a trigger and store all changes to the table in a separate table. You can then use this table to get the revision history.

Answer 2

SQL Server doesn't keep track of changes so it can't tell you this.

The only way you may be able to do this is if you had a copy of all the scripts applied to the database.

In order to be able to capture this information in the future you should look at DDL triggers (v2005+) which will enable you to record changes.

Answer 3

You can get last modify date or creation date of object in SQL Server.

For examle info on tables:

SELECT * FROM sys.objects WHERE type='U'

More info on msdn

Number of pages can be fetched from sys.database_files.

Check documentation