Recent changes table structure

  • Good Morning

    How to find if there was any recent changes done to the table structure?

  • Do a DB schema compare against what you have stored in source control to identify any 'drift'.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Yep. What Phil says.

    Otherwise, you can set up an Extended Events session to capture object_altered. Same goes for created and dropped.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • coolchaitu wrote:

    Good Morning How to find if there was any recent changes done to the table structure?

    A quick way to check is to query against sys.tables in a database.  Every table has a "modify_date" column.  That date will either be the create_date if the table has never been modified or it will contain the date of the last ALTER TABLE done.

    It won't tell you what the changes are but you can write a pretty simple query to tell you when the last change was.  If you write a query to check if there are any modify_date's within the last seven days and write a quick little job that will send you an email when such a date appears, you can have a really good "it just happened" notification system.

    If you want to know what the changes actually are, then you'd have to have a system in place to do checks or capture changes like Phil and Grant what have respectively posted about above.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Hi Jeff,

    Does the "modify_date" column gets modified when DML changes also occur, or only DDL changes?

     

  • coolchaitu wrote:

    Hi Jeff,

    Does the "modify_date" column gets modified when DML changes also occur, or only DDL changes?

    Why not run a quick test and find out for yourself?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • coolchaitu wrote:

    Hi Jeff,

    Does the "modify_date" column gets modified when DML changes also occur, or only DDL changes?

    You should do a search on sys.tables instead of taking my word for it.  I'll also advise that there will be an "inherited columns" link that you can and should click on to see any columns that are missing.  This will also teach you a bit about sys.objects, which is one of "those" things that is an absolute MUST to learn how to use.  In fact, you should look that up, as well.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • There is also the schema changes report available - that shows not only changes to tables but will also show changes to any objects and who made the change.  However, since it pulls the data from the default trace - it may not go back far enough if you have enough activity to roll the default trace.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 8 posts - 1 through 8 (of 8 total)

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