Find the date/time a table's column was created

Question

How can you query SQL Server for the creation date for a SQL Server 2005 table column?

I tried the sp_columns [tablename] to get that information, but the creation date wasn't included in this stored procedure.

How can this be done?

Answer 1

SQL Server does not keep track of specific changes to tables. If you want or need this level of detail, you need to create a DDL Trigger (introduced in SQL Server 2005) that can trap certain specific events or even classes of events, and log those changes to a history table that you create.

DDL Triggers are "after" triggers; there is no "instead of" option. However, if you want to disallow an action, you can just issue a ROLLBACK and that will cancel what happened.

The MSDN page for DDL Triggers has a lot of good information regarding how to trap either specific events (i.e. ALTER TABLE) and using the EVENTDATA function, which returns XML, to get the specifics of what event fired the trigger, including the exact SQL query that was executed. In fact, the MSDN page for Use the EVENTDATA Function even has simple examples of creating a DDL trigger to capture ALTER TABLE statements (in the " ALTER TABLE and ALTER DATABASE Events" section) and creating a DDL trigger to capture the events to a log table (in the "Example" section). Since all ALTER TABLE commands will fire this trigger, you need to parse out which ones are specific to what you are looking for. And, maybe now that you know that this is an option, capturing more than just adding columns is desired (i.e. dropping columns, changing the datatype and/or NULLability, etc).

It should be noted that you can create a DLL trigger ON ALL SERVER for database-scoped events such as ALTER_TABLE.

If you want to see the structure of the XML for any event or event class, go to:

http://schemas.microsoft.com/sqlserver/2006/11/eventdata/

and click on the "Current version:" link. If you want to see a specific event or event class, just do a search (usually Control-F in the browser) on the event name that would be used in the trigger's "FOR" clause (including the underscore). The following is the schema for the ALTER_TABLE event:

<xs:complexType name="EVENT_INSTANCE_ALTER_TABLE">
<xs:sequence>
<!--  Basic Envelope  -->
<xs:element name="EventType" type="SSWNAMEType"/>
<xs:element name="PostTime" type="xs:string"/>
<xs:element name="SPID" type="xs:int"/>
<!--  Server Scoped DDL  -->
<xs:element name="ServerName" type="PathType"/>
<xs:element name="LoginName" type="SSWNAMEType"/>
<!--  DB Scoped DDL  -->
<xs:element name="UserName" type="SSWNAMEType"/>
<!--  Main Body  -->
<xs:element name="DatabaseName" type="SSWNAMEType"/>
<xs:element name="SchemaName" type="SSWNAMEType"/>
<xs:element name="ObjectName" type="SSWNAMEType"/>
<xs:element name="ObjectType" type="SSWNAMEType"/>
<xs:element name="Parameters" type="EventTag_Parameters" minOccurs="0"/>
<xs:element name="AlterTableActionList" type="AlterTableActionListType" minOccurs="0"/>
<xs:element name="TSQLCommand" type="EventTag_TSQLCommand"/>
</xs:sequence>
</xs:complexType>

Here is a very simple test to see how this works and what the resulting EventData XML looks like:

IF (EXISTS(
   SELECT *
   FROM   sys.server_triggers sst
   WHERE  sst.name = N'CaptureAlterTable'
   ))
BEGIN
  DROP TRIGGER CaptureAlterTable ON ALL SERVER;
END;
GO

CREATE TRIGGER CaptureAlterTable
ON ALL SERVER -- capture events for all databases
FOR ALTER_TABLE -- only capture ALTER TABLE events
AS
  PRINT CONVERT(NVARCHAR(MAX), EVENTDATA()); -- Display in "Messages" tab in SSMS
GO

First we create a simple, real table in tempdb (these events are not captured for temp tables):

USE [tempdb];
CREATE TABLE dbo.MyAlterTest (Col2 INT NULL);

Next we add a column. We do this from a different database to make sure that the XML captures the database where the object exists instead the current database. Please note the casing of the words alTeR Table tempDB.dbo.MyALTERTest ... DATEcreated to compare with what is in the XML.

USE [master];
alTeR Table tempDB.dbo.MyALTERTest ADD DATEcreated DATETIME NOT NULL;

You should see the following in the "Messages" tab (comments added by me):

<EVENT_INSTANCE>
  <EventType>ALTER_TABLE</EventType>
  <PostTime>2014-12-15T10:53:04.523</PostTime>
  <SPID>55</SPID>
  <ServerName>_{server_name}_</ServerName>
  <LoginName>_{login_name}_</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>tempdb</DatabaseName> <!-- casing is based on database definition -->
  <SchemaName>dbo</SchemaName>
  <ObjectName>MyAlterTest</ObjectName> <!-- casing is based on object definition -->
  <ObjectType>TABLE</ObjectType>
  <AlterTableActionList>
    <Create>
      <Columns>
        <Name>DATEcreated</Name> <!-- casing is taken from executed query -->
      </Columns>
    </Create>
  </AlterTableActionList>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE"/>
    <CommandText>alTeR Table tempDB.dbo.MyALTERTest ADD DATEcreated DATETIME NOT NULL;&#x0D;
</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

It would have been nice if the per-column details (i.e. NULL / NOT NULL, datatype, etc) were captured instead of just the name, but if need be, those can be parsed out of the CommandText element.