Ambiguity with sys.object create_date and Modify_date columns.

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gail.

    I changes it though management studio GUI. Does it matter?

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply