How to change table, column name of existing table (with data, indexes, constrains...)
-
Hi,
I've to change the name of some tables (include their columns name) in a working database. These tables contains lot of data and currently work well and I want to find a way to do this task (manually or with script would be better) without breaking related objects like indexes, constrains, foreign keys... I mean that, reference to table, column names in these object should be changed automatically or I should change them in correct order.
What should be the best way to do this task? Thanks for your reply
.Tien,
-
Check out the sp_rename stored procedure in BOL. Haven't used it myself, but it looks as though you can script your required changes fairly easily if you use it.
Regards
Phil
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 -
Do a search in the script section here. As this is not a too exotic task, it's likely that you will find something useful there.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
What you are about to do can have very serious implications as you may be breaking your database without knowing it (which is why sp_rename posts a warning when you use it). Any objects (views, user defined functions, stored procedures) can be made unworkable as a result of table and or column name changes and you’ll never know it until you try to use them. A mandatory check of any dependant objects is necessary however it is not conclusive and can be very laborious. This is the reason I build a copy of the database using the source code to see if my changes break anything. This is a fundamental part of the methodology I have used with total success since it has been created. The white paper has also been published on this site where it has received excellent reviews:
http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Approach_To_Do_Change_Mgt.pdf
I created the tool DB Ghost to facilitate this methodology and have never made a change to a database and introduced an error since using this methodology and the tool I created.
Can you afford to introduce breakages into your database and risk data loss and maybe even job loss?
regards,
Mark Baekdal
+44 (0)208 241 1762
Living and breathing database change management for SQL Server
-
Hm, and altruistic as you are, this tool is provided for free, right?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hello Frank.
The tool is not for free, I can't afford for it to be. This is my full-time job so as my wife and children cannot live on air - I have to charge for my services which have an excellent return on investment I might add.
How do you feed your family and yourself?
regards,
Mark Baekdal
+44 (0)208 241 1762
Living and breathing database change management for SQL Server
-
The tool is not for free, I can't afford for it to be. This is my full-time job so as my wife and children cannot live on air - I have to charge for my services which have an excellent return on investment I might add.
What a suprise, Mark! No offense, but I have a mixed feeling, when it comes to use such an online community as some kind of pre-sales support. But after all, this here is not my site, and SSC depends on advertising. I have a different approach, and would have deleted such a posting after become aware of it.
How do you feed your family and yourself?
As you might have guessed, we also do not live on air. You might want to have a look at my profile and see what I'm doing fulltime.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
The information can help "Voi Coi" if he chooses to use it. No information definetly doesn't. I totally believe the application can have a positive effect here as I have faced the problem myself which is one of the reasons I built the tool. Although I am now termed a vendor, I am still a DBA/Database programmer and I enjoy technology as much as any other self respecting geek - so I like to participate. You always have the option to build your own tool/tool set to facilitate the methodology I use, however I think you'll find it to be exponantially cheaper to purchase the software.
You could of course ignore me and possibly miss out on what I think is the best software and process I have ever created.
regards,
Mark Baekdal
+44 (0)208 241 1762
Living and breathing database change management for SQL Server
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply