Renaming tables _new suffix

  • Thanks for taking the time to look at my current situation.  All feed back is welcome and thanks again

    I have approx 650 tables in one db and i want to copy them all to a new name with _new added to original name.  The trick is i would like to make this so that tableA is copied to tableA_new and i want tableA_new to have all the objects of tableA (as if it was an exact clone) and for tableA to remain functional.

    I cant think of any way of doing this to keep all keys, triggers, indexes, etc without doing each table separately one by one and i know there has to be a better way.

    Please let me know all thoughts and if you have some code that does this that too would be appreciated as i can modify to my environment.  Thank you

    Dheath

     

    • This topic was modified 4 years, 11 months ago by  DHeath.

    DHeath

  • Work out the syntax for renaming a single table and then write some T-SQL which queries sys.tables & constructs that T-SQL for all tables, as a SELECT.

    You can then cut & paste the results into SSMS, ready for execution.

    Please explain this part:

    ... and for tableA to remain functional

    If it's been renamed, it will no longer exist - it is therefore not functional at all.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Phil... thanks for that as actually it is copying the table and keeping the same name with _new added.  So for the mis hap..will correct my self in the post.. Thanks

    DHeath

  • DHeath wrote:

    Phil... thanks for that as actually it is copying the table and keeping the same name with _new added.  So for the mis hap..will correct my self in the post.. Thanks

    No problem. I've realised that my approach is too simplistic & will not handle constraints or triggers, so I'm thinking of better ways to get to where you want.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • One thing I've learned through experience is not to use the word 'new' in any object names. It may seem like a good idea now, but in three years' time ... you'll be refactoring.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Can you provide a bit of background as to why you want to do this?

    Seems like it will create a lot of clutter in your DB ... others here may have alternative ideas which you have not considered.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • If you press F7 in SSMS you can select multiple tables and script the create for all of them, in one go.

    You would then have to edit the script but this would be little more than pasting "_new", or whatever suffix you choose, onto the end of the table names, PKs etc... So would still probably involve pressing the paste key about 2,000 times. But faster than scripting each one individually.

  • Johnathan LMAO.... loved your response with the comment *** So would still probably involve pressing the paste key about 2,000 times. But faster than scripting each one individually.*** too funny because its most likely what will end up happening 🙁

    Thank you very much for your input as its greatly appreciated and brought a smile to my face

    DHeath

  • Do you want the new tables to be empty?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • script tables with data..

    and play with this , eventually you will get there.

     

  • or use vi  editor in liunx.. or notepad ++  https://community.notepad-plus-plus.org/topic/12371/regex-how-would-i-do-this/2

    good luck

  • Phil,  Yes i would like the new tables to be empty once they are created.

    DHeath

  • how about putting all the new tables into a different schema to avoid confusion in SSMS?

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 13 posts - 1 through 12 (of 12 total)

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