Unanswered: Ambiguity with sys.object create_date and Modify_date columns.
I’m using a store procedure ‘sp_msForEachdb’ and trying to capture LastModifedDate for all the columns within the databases in my environment. Here is my code to accomplish it
obj.name as TableName,
obj.object_id as ID,
col.name as ColumnName,
convert (varchar(10), obj.create_date, 103) as ColumnCreationDate,
convert (varchar(10), obj.modify_date, 103) as LastModifiedDate
from ?.sys.objects obj
inner join ?.sys.columns col
on obj.object_id = col.object_id
where type_desc = ''USER_TABLE''
order by LastModifiedDate'
Now if you guys notice I used sys.objects to capture object ‘Modify_date’ and ‘create_date’. My code works fine. However there is little hiccup. May be this how SQL Server works I’m not sure and this is where I need you experts help. Let say if I change the Column TYPE or LENGTH. By running the above code I can see which table column TYPE or LENGTH has been a change at what date. My problem is though sys.object gives me ‘Modify_date’ but it also changes ‘create_date’ of the column. Logically ‘create_date’ should stay the same (to the date when the column was originally created) and only ‘Modify_date’ should get change. But this is not happening here with sys.object. To test it make some change in any of your column TYPE or LENGTH & you’ll notice not only ‘Modify_date’ get replace with today’s date but also ‘create_date’ get change with today’s date.
Now does anybody know why both ‘Modify_date’ & ‘create_date’ are getting change? Again to me only ‘Modify_date’ should get change not ‘create_date’. Please help
I just tried this (on SQL Server 2008) and I don't see what you see. When I ALTER a column type or size only the modify_date changes, not the create_date.
I wonder if you are using Management Studio GUI to make your changes? The SSMS GUI is the tool from hell when it comes to schema changes! It will happily drop and create whole tables and columns if you aren't careful and that could explain what you see. If you care about what's happening to your tables then don't use SSMS to do table changes at all.
If I'm wrong and you are using ALTER TABLE statements then please post enough code to reproduce the problem (CREATE and ALTER statements) and tell us what version and build of SQL Server you are using.