Find the date / time the table column was created

Question

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

I tried sp_columns [tablename] to get this information, but the creation date was not included in this stored procedure.

How can I do that?

Answer 1

In this system table called sys.Columns, you can get information about the columns. if you want to see the columns of a particular table, you can do the following:

 SELECT col.* from sys.objects obj inner join sys.columns col on obj.object_Id=col.object_Id and obj.Name=@tableName 

Or you can get the table information as follows:

 SELECT * FROM sys.objects WHERE Name=@tableName 

but I could not find the column creation date information.

Updated: This may help .

Answer 2

SQL Server does not track specific table changes. If you need or need this level of detail, you need to create a DDL trigger (introduced in SQL Server 2005) that can capture specific events or even event classes and write these changes to the created history table.

DDL triggers are “after” triggers; there is no option to replace However, if you want to deny an action, you can simply send a ROLLBACK and undo what happened.

The MSDN page for DDL Triggers contains a lot of good information on how to catch certain events (i.e. ALTER TABLE ) and use EVENTDATA , which returns XML to find out in which event the trigger is fired, including the exact SQL query that was executed . In fact, on the MSDN page for Use the EVENTDATA function, there are even 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 write events to the log table (in the "Example" section) . Since all ALTER TABLE commands fire this trigger, you need to analyze which of them and, perhaps now that you know that this is an option, you need more than just adding columns (e.g. deleting columns, changing data type and / or NULLability etc.).

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

If you want to see the XML structure for any event or event class, follow the link:

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

and click on the link "Current version:". If you want to see a specific event or class of events, just search (usually Control-F in the browser) for the name of the event that will be used in the FOR trigger clause (including underscore). The following is a diagram of 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 XML-derived XML code 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 recorded for temporary tables):

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

Then add a column. We do this from another database to make sure that XML captures the database where the object exists, and not the current database. Note the cover of the words alTeR Table tempDB.dbo.MyALTERTest ... DATEcreated for comparison with what is in XML.

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

You should see the following on 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 be nice if the column data (i.e. NULL / NOT NULL, data type, etc.) was captured instead of the name, but if necessary, it can be parsed from the CommandText element.

Answer 3

I think there is no way to get the changes or the creation date of the individual columns as such. The queries specified in this and this return the dates associated with the table containing the column, not the column. Because the sys.columns table has the same identifier for all columns in the table. You can verify this by running this query

 select col.object_id, col.name, col.column_id from sys.columns col where col.object_id = (select o.object_id from sys.objects o where o.Name = @tableName) 

Answer 4

Beat was late for the party, but found something that helped me. If you can say that the column is the last table change, then sys.tables(modify_date) healthy.

Answer 5

 SELECT so.name,so.modify_date FROM sys.objects as so INNER JOIN INFORMATION_SCHEMA.TABLES as ist ON ist.TABLE_NAME=so.name where ist.TABLE_TYPE='BASE TABLE' AND TABLE_CATALOG='dbName' order by so.modify_date desc; 

Answer 6

 SELECT obj.create_date from sys.objects obj inner join sys.columns col on obj.object_Id=col.object_Id WHERE col.name = @columnName and obj.Name=@tableName 

based on the previous answer but giving only the column creation date

Answer 7

I do not think that this information is available if you do not have what can view transaction logs, for example Log Explorer. This is not a tsql thing.