Post reply

finding Structure of the table

  • September 23, 2011 at 6:13 am

    #248587

    Hi,

    Is it possible to find last modified date of the table and previous structure of the table after it is altered...?

    Thanks

    Thanks,
    Charmer

  • September 23, 2011 at 6:20 am

    #1385652

    prakash 67108 (9/23/2011)

    Hi,

    Is it possible to find last modified date of the table and previous structure of the table after it is altered...?

    Thanks

    you can get the last modified date if someone ran the ALTER TABLE command:

    the column modify_date:

    select name, create_date,modify_date from sys.tables --sys.objects as well

    for the previous structure, no. if you don't have a DDL trigger that is actually capturing structure definitions, then all you can do is reverse engineer changes you discover.

    you can find some changes in the default trace, but it rolls over pretty quickly, so only recent changes can be found.

    There's quite a few forum posts on DDL triggers, but what are you trying to accomplish specifically? is it fingerpointing (who did the change)? or tracking changes or what?

    here's the quickest way i know to get to the default trace:

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 23, 2011 at 6:22 am

    #1385653

    Hello,

    You can get the last modified date from sys.Tables.

    Select * from sys.Tables.

    As for previous structure, I am not sure whether you could get it back unless you have a source control / version control.

    Hope that helps.

    Thanks,

    Vasu

  • September 23, 2011 at 6:33 am

    #1385658

    Hi,

    thanks for that buddy....i got about the modified details....but is it not possible to get the table structure of previous....i have source control tooo....

    Thanks,
    Charmer

  • September 23, 2011 at 7:47 am

    #1385737

    If you've got source control then surely you can just get previous versions out of the source control system (that's kinda one of the reasons for having source control...)

    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

  • September 23, 2011 at 8:41 am

    #1385801

    select a.object_id

    ,SCHEMA_NAME(schema_id) as [SCHEMA]

    ,a.name as [Procedure]

    ,type

    ,type_desc

    ,create_date as [Created Date & time]

    ,modify_date as [Modified Date & time]

    from sys.objects a

    Where type = 'u'

    BUT NOT SURE ABOUT STRUCTURE HISTORY ... I WILL SEARCH ABOUT IT

  • September 23, 2011 at 8:53 am

    #1385812

    shivendrakumaryadav60 (9/23/2011)

    BUT NOT SURE ABOUT STRUCTURE HISTORY ... I WILL SEARCH ABOUT IT

    SQL doesn't keep history unless you set something up to do so.

    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 7 posts - 1 through 6 (of 6 total)

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