December 15, 2009 at 9:58 am
Hello experts,
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
sp_msForEachdb
'select
''?'',
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
Thanks in advance.
December 15, 2009 at 10:00 am
How did you change the length/type of the columns? ALTER TABLE or via the management studio GUI?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 15, 2009 at 10:45 am
Gail.
I changes it though management studio GUI. Does it matter?
December 15, 2009 at 10:57 am
If you look at the script that management studio GUI runs, you'll notice that it's dropping and recreating the table for changes like that. It doesn't just alter the table, it recreates it. Hence the create data reflects that change because the table really was created then.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply