Post reply

Table Structure Change

  • August 20, 2003 at 12:19 pm

    #106024

    When Bringing in a table from a source DB into a Table in SQL that has the same structure that already exists, is there a way I can see if the structure of the source table has been changed from one day to the next.

    (Example: A new column has been added)

    Edited by - mightyjoemad on 08/20/2003 12:20:21 PM

    Edited by - mightyjoemad on 08/20/2003 12:20:58 PM

  • August 20, 2003 at 1:42 pm

    #470657

    Are you appending the data to the existing table? Or are you truncating the table, recreating it and loading it?


    Aurora

  • August 20, 2003 at 1:52 pm

    #470658

    I think this is more on the basis of SQL Statements. Its not really DTS.

    I want to capture a structure change of a table in another table. Like an Audit table.

    Thanks FOr the help. Ill search the web for ohter ideas.

    Edited by - mightyjoemad on 08/20/2003 1:52:47 PM

  • August 21, 2003 at 1:05 am

    #470707

    Hi there

    I'm by no means a guru, but have thought about this issue myself recently. Haven't done it yet, but maybe my initial train of thought might help you in a way.

    I thought of 1) creating a permanent table storing the relevant information from sysobjects, syscolumns and sysindexes of the table object I'm interesting in auditing.

    Then I would 2) write a stored procedure to read the current information into a table variable or temp table and use a script I found here on this site to compare the data in two tables to highlight or record any differences.

    Only problem with this is that you DON'T have the username that changed the table and that you have to run the script manually or through a scheduled job once or more per day to track changes. But what else do you do if you can't have triggers on system tables???

    Sorry if this doesn't help you in ANY way, but maybe someone else there will come up with the solution and I might benefit from it as well

    Nikki Pratt

    Development DBA


    Nikki Pratt
    Development DBA

  • August 21, 2003 at 12:40 pm

    #470843

    Thanks NProatt. That was a good idea.

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

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