SQL Server 2005 Table Alter History

sql server track changes to a table
how to check table history in sql server
how to find table changes in sql server
sql server change data capture
how to find who updated the table in sql server
how to find database changes in sql server
sql alter table
sql server audit table data changes

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.

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.

ALTER TABLE (Transact-SQL), Modifies a table definition by altering, adding, or dropping columns and constraints. OFF | ON [ ( HISTORY_TABLE = schema_name . history_table​_name [ Partition 2 (has data): '2004-01-01' <= OrderDate < '2005-01-01'  APPLIES TO: SQL Server Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse. Modifies a table definition by altering, adding, or dropping columns and constraints. ALTER TABLE also reassigns and rebuilds partitions, or disables and enables constraints and triggers.

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()
                     +'; '+@EventData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(250)')
                     +'; '+@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(250)')
                     +'; '+@EventData.value('(/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.

Track Data Changes, Change data capture provides historical change information for a user table by capturing both the fact that DML changes were made and the  The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table. To add a column in a table, use the following syntax: The following SQL adds an "Email" column to the "Customers" table: To delete a column in a table, use the

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(<database name>[,{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)  

How to read the transaction logs for a specific table in SQL server?, SQL Server 2005 will increase your programming options, productivity, analysis, and database management. If you have some basic knowledge of relational  APPLIES TO: SQL Server 2016 and later Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse . With system-versioned temporal tables, the history table may increase database size more than regular tables, particularly under the following conditions: You retain historical data for a long period of time

Learn how to Add, Remove, or Modify values in a SQL Table , In this article we look at how we can add, modify, and remove data with the INSERT, SQL Server Management Studio and the AdventureWorks2012 database. The INSERT statement is used to add one or more rows to a database table. of Human Resources wants to remove all pay history changed before 2002. I have a table in SQL Server 2012. The table has few columns. Now I need to check for which column and what is the DDL script has been executed on this table. Or if it is possible to get what is the latest ALTER has been executed for this table. Thanks in advance.

Introduction to Change Data Capture (CDC) in SQL Server 2008 , SQL Server 2005 introduced the new features of 'after update', 'after cdc.​ddl_history – This table contains history of all the DDL changes  There is no ALTER statement you can run that will insert it at the beginning. Even if you use the SQL 2005 Management Studio, and you insert a column at the beginning, that's what it is doing behind the scenes when you click Save. (rename the original table, create the new table, insert the data)

SQL Server Rename Table By Examples, This tutorial shows you how to rename a table using Transact SQL and SQL Server Management Studio. Temporal Tables. SQL Server 2016 introduced support for temporal tables (also known as system-versioned temporal tables) as a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time.

Comments
  • Thanks KM. But I already tried ddl triggers which work fine and can easily be used the log info if only new columns are added to a table. But I think it would be a tedious job to use the TSQL Commands in the log for alterations like renaming a column that has foreign key references or changing the length of a varchar type column etc. And storage will soon become an 'issue' as Unreason said.