Date Column was created

Question

Hi,

Do you know if there is a way to get the creation date for a SQL Server 2005 table column?

I tried the sp_columns [tablename] to get some table information but no luck.

Thank you.

Answer 1

There's this system table named sys.Columns that you can get columns information from it. if you want to see columns of a particular table you can do as follows:

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 table information like this:

SELECT * FROM sys.objects WHERE Name=@tableName

but I couldn't find any information on creation date of a column.

Updated: This might help.

Answer 2

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

building on the previous answer, but giving only the create date of the column

Answer 3

I think there is no way of getting modification or creation date of individual columns per se. The queries given in this and this answer returns the dates related to Table containing the column not the column. Because sys.columns table have same id for all the 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

I don't think this information is available unless you have something that can browse the transaction logs, like Log Explorer. It's not a tsql thing.