Renaming SQL Server database objects and changing object owners

Rename a DatabaseA 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.Example: rename database from Test1 to Test2

sp_renamedb ‘Test1’ , ‘Test2
or
sp_rename ‘Test1’, ‘Test2’, ‘DATABASE’;

Rename an ObjectAnother 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 IndexIndexes 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 ColumnRenaming 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 OwnerChanging 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/SchemaTo 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’;

Source : http://www.mssqltips.com/tip.asp?tip=1396

Advertisements

About alamzyah
Name : Alamsyah Nick Name : Alamzyah Place of Birth : Jakarta, 04 June 1983 sex : Male Religion : Moslem Region : Jakarta, Indonesia Specialist : IT, Computer mail : alamzyah@gmail.com

5 Responses to Renaming SQL Server database objects and changing object owners

  1. essays says:

    Well done, my essay was a real success among tutors. I am so satisfied with your work.

  2. Starr Wackenheim says:

    I have been surfing online more than 3 hours today, yet I never found any interesting article like yours. It is pretty worth enough for me. Personally, if all webmasters and bloggers made good content as you did, the net will be much more useful than ever before.

  3. Wow! Even though I certain this stuff is out of my league. Its still very cool.

  4. I dont agree with this completely but it brings up some excellent points.

  5. I got more than I bargained for with this blog. Attention grabbing topic. Will follow.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: