December 11, 2019 at 9:49 pm
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
DHeath
December 11, 2019 at 10:03 pm
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 11, 2019 at 10:12 pm
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
December 11, 2019 at 10:17 pm
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 11, 2019 at 10:24 pm
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 11, 2019 at 10:27 pm
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 11, 2019 at 10:40 pm
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.
December 11, 2019 at 10:47 pm
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
December 12, 2019 at 1:47 pm
Do you want the new tables to be empty?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 13, 2019 at 1:16 am
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
December 17, 2019 at 5:36 pm
Phil, Yes i would like the new tables to be empty once they are created.
DHeath
December 17, 2019 at 7:33 pm
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