Renaming SQL Server database objects and changing object owners

By: Greg Robidoux | Read Comments (2) | Print Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com. Related Tips: More

Problem
As with most things in life, nothing ever stays the same.  This is true with SQL Server and also with your applications that utilize SQL Server. The way applications and databases were originally designed may or not meet your current or future needs and therefore there is a need to change what you thought was the perfect solution when it was first rolled out.  Changes that you make on a daily or weekly basis probably are embedded in the application or database code (stored procs, triggers, etc...), but one change that you may face is the need to rename database objects that already exist.  What options are available to rename database objects?

Solution
SQL Server offers many utilities that you may or may not be aware of and here are specific tools that allow you to rename objects that exist in a SQL Server instance.  Each of these commands allows you to make changes to database objects or change ownership of certain objects.  The following grid outlines the need, the command and some sample code.

Need

Rename a Database A database can be renamed in one of several ways.  The two commands that SQL Server offers are sp_renamedb and sp_rename.  In addition, if you are using SQL Server Management Studio you can also change a database name via the GUI.  Another option is to detach and reattach the database and rename the database on the attach. For more information take a look at this previous tip How to rename a database Example: rename database from Test1 to Test2 sp_renamedb 'Test1' , 'Test2 or sp_rename 'Test1', 'Test2', 'DATABASE';

Rename an Object Another task that you may need to do is to rename an existing table, stored procedure, trigger, etc..  Again this can be done in several ways.  You can use the sp_rename stored procedure or use SQL Server Management Studio by right clicking on the object and selecting rename. Another option is to create a new object and drop the old object. This could be any object that exists with SQL Server (table, stored procedure, trigger, etc...) Example: rename object Test1 to Test2. sp_rename 'dbo.Test1', 'Test2', 'OBJECT';

Rename an Index Indexes can be renamed using the sp_rename option or again you can create a new index with the new name and drop the old index.  Again this can be done using SQL Server Management Studio. Example: rename an index from Test1 to IX_Test1 sp_rename 'dbo.Test.Test1', 'IX_test1', 'INDEX';

Rename a Column Renaming a column can be done using the sp_rename stored procedure or you can use ALTER TABLE to add a new column with the new name, move the data to the new column and then drop the old column. This can also be done using SQL Server Management Studio, by right clicking on the column name and selecting rename. Example: rename column Product in table Test1 to ProductName sp_rename 'dbo.Test1.Product', 'ProductName', 'COLUMN';

Change Database Owner Changing database ownership can be done by using the sp_changedbowner. This can also be done using SQL Server Management Studio under the database properties. Example: change the current database owner to DBadmin sp_changedbowner 'DBadmin'

Change Object Owner/Schema To change the ownership of objects you can use the ALTER SCHEMA command for SQL 2005 or the sp_changeobjectowner for SQL 2000. Example: change the schema for table Test from the dbo schema to schema TestSchema --SQL 2005 ALTER SCHEMA TestSchema TRANSFER dbo.Test --SQL 2000 sp_changeobjectowner 'Test', 'TestSchema';

(note: when using SQL Server Management Studio to make these changes, the GUI is just calling the sp_rename stored procedure)

One thing to keep in mind is that when you use these commands or the GUI to rename objects, the change does not get propagated to other objects that are dependent upon the object that you renamed, therefore you will need to make modifications to these other objects in order for them to use the renamed object. As you can see this is a simple way of making these changes, but it could also have negative impact on your application and database.  So before you start renaming objects make sure you know the impact of the change first.

Next Steps

• Next time you need to make a name change, remember these different options that exist.
• To determine which objects are dependent upon other objects take a look at this tip, Listing SQL Server Object Dependencies to help find the answers

 

Related Tips: More | Become a paid author

Last Update: 12/21/2007

Share:

Share

Tweet

Free SQL Server Learning Introduction to SQL Azure Join Herve Roggero on Wednesday, June 13 @ 3:00 PM EST to learn more.

Comments and Feedback:

Wednesday, March 17, 2010 - 3:33:36 AM - elizas	Read The Tip
Please visit the link below.It would answer your query. http://www.mindfiresolutions.com/Renaming-Databse-Objects-859.php

Tuesday, April 17, 2012 - 1:01:51 PM - webpos	Read The Tip
Hi, I have a question How can I change a dbo. table system object paramenter to TRUE instead of fale. webpos

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Try the free performance monitoring tool from Idera! Quickly and accurately deploy database changes with Red Gate's SQL Compare - the industry standard comparison and deployment tool. What grade do you think your SQL Servers get? Find out with Edgewood's SQL Server Health Check starting at $995. Find and Fix SQL issues with Foglight Performance Analysis. Get a free copy. Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now. The SQL Server Security THREAT - It’s Closer Than You Think