October 3, 2010 at 11:15 pm
Is there a script to change all the user created tables? for example, a database created with 5 tables (table1, table2,table3,table4,table5) and later wants to rename them to (table11, table21, table31,table41,table51). I have more than 150 tables in the actual database so for testing purpose wants to suffix each table name with "1". Is it possible?
October 4, 2010 at 6:29 am
jamesmario (10/3/2010)
Is there a script to change all the user created tables? for example, a database created with 5 tables (table1, table2,table3,table4,table5) and later wants to rename them to (table11, table21, table31,table41,table51). I have more than 150 tables in the actual database so for testing purpose wants to suffix each table name with "1". Is it possible?
Sure. sp_rename is the tool for the job.
Just write a query that looking at INFORMATION_SCHEMA.TABLES creates a sp_rename statement per table you want to rename then, manually check created script and execute if Okay.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply