SQL Server 2005 Table Alter History

Question

Hi. Does SQL Server maintains any history to track table alterations like column add, delete, rename, type/ length change etc? I found many suggest to use stored procedures to do this manually. But I'm curious if SQL Server keeps such history in any system tables? Thanks.

Answer 1

The transaction logs store all this information and the DBCC LOG command should let you view that, but it's an undocumented command.

DBCC LOG([,{0|1|2|3|4}])
0 – Basic Log Information (default)
1 – Lengthy Info
2 – Very Length Info
3 – Detailed
4 – Full Example
Syntax:
DBCC log (MY_DB, 4)

Answer 2

No it doesn't keep this history. Depending upon your recovery model you might be able to retrieve some useful info from the log files as ho suggests.There is a system function called fn_dblog that is easier to use than DBCC log but I suspect that trying to use it would still be quite painful from Andrew's answer here http://stackoverflow.com/questions/2431575/ms-sql-2008-howto-read-from-log/2431621#2431621

Answer 3

in SQL Server 2005 and up you can create a database level trigger to track table changes. Use something like:

CREATE TRIGGER [YourDatabaseTrigger]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS

DECLARE @EventData      xml
DECLARE @Message        varchar(1000)
SET @EventData=EVENTDATA()

INSERT INTO YourLogTable 
    (EventDateTime,EventDescription) 
    VALUES (GETDATE(),SUSER_NAME()
                     +'; '[email protected]('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(250)')
                     +'; '[email protected]('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(250)')
                     +'; '[email protected]('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
           )
RETURN
GO

ENABLE TRIGGER [YourDatabaseTrigger] ON DATABASE

here is some simple output from the log:

select * from YourLogTable
EventID     EventDateTime           EventDescription
----------- ----------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------
1           2010-04-06 08:25:47.333 sa; TABLE; YourLogTable2; create table YourLogTable2 (EventID int primary key identity(1,1),EventDateTime datetime, EventDescription varchar(max))
2           2010-04-06 08:25:55.113 sa; TABLE; YourLogTable2; drop table YourLogTable2

(2 row(s) affected)

you could expand the log to contain more columns, or just dump everything within a one like in this simple example.

Answer 4

This kind of information is not retained in any RDBMS by default because it could increase the required storage requirements by orders of magnitude, and could severely decrease performance.

Triggers can do this for you. Triggers are not considered manual method - they are a core part of database design.