Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

SQL etc

Posted Nov 2, 2001

SQL "How To's" Part 4

By Sumit Dhingra

Hi guys. I am back again with SQL "How To's" Part IV. As a recap, this series of articles is an attempt to begin a reference material that saves you a lot of time and frustration in situations where you might wonder "How do I do this?". It's my hope that this resource will help provide quick and helpful to-the-point answers to common SQL Server questions. So, I hope you will find this fourth part to be as useful as Parts I, II, and III.

In this edition we'll be taking a look at how to rename Objects in SQL Server. You might say that's easy, just go to the enterprise Manager, right click an object and rename it. Well, there is a problem with this approach. What if as a DBA you are managing more than one server -- Development, Test/QA, Production etc. If you make a change on one server, you have to propagate that change to all the servers. If you use Enterprise Manager to make a change on one, you'll have to use the same on other servers as well, which could be an arduous task.

Then what's the solution? Scripting!! Prepare a script of changes and apply that to all the servers on which you want the change to happen. Following are some of the code samples to script rename statements for different objects. All the code samples are based on Pubs Database.

1) How to Rename a Table

Renaming table 'Sales' to 'Orders'.

IF Exists (Select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Exec sp_rename 'Sales', 'Orders'
IF @@Error <> 0
Raiserror('Failed to rename Table Sales to Orders',16,1)
Print 'Table Sales Renamed to Orders'
Print 'Table Sales does not exist'

2) How to Rename a Table Column

Renaming column 'Qty' in 'Orders' table to 'Quantity'.

IF (SELECT COLUMNPROPERTY( OBJECT_ID('Orders'),'Quantity','AllowsNull')) IS NULL
Exec sp_rename 'Orders.[Qty]', 'Quantity', 'COLUMN'
IF @@Error <> 0
Raiserror('Failed to rename column Qty to Quantity',16,1)
Print 'Column Qty Renamed to Quantity'
Print 'Column Quantity already exists'

3) How to Rename a Primary Key

Renaming Primary Key 'UPKCL_sales' in 'Orders' Table to 'PK_Orders'.

IF Exists (select * from sysobjects where id = object_id(N'[UPKCL_sales]')
and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
Exec sp_rename 'UPKCL_sales', 'PK_Orders', 'OBJECT'
IF @@Error <> 0
Raiserror('Failed to rename PK UPKCL_sales to PK_Orders',16,1)
Print 'PK UPKCL_sales Renamed to PK_Orders'
Print 'PK UPKCL_sales does not exist'

4) How to Rename a Foreign Key

Renaming Foreign Key 'FK__Titleauth__au_id__164452b1' in 'Titleauthor' Table to 'FK_Titleauthor_Author_auid'.

IF Exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Titleauth__au_id__164452b1]')
and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
Exec sp_rename 'FK__Titleauth__au_id__164452b1', 'FK_Titleauthor_Author_auid', 'OBJECT'
IF @@Error <> 0
Raiserror('Failed to rename FK FK__Titleauth__au_id__164452b1 to FK_Titleauthor_Author_auid',16,1)
Print 'FK FK__Titleauth__au_id__164452b1 Renamed to FK_Titleauthor_Author_auid'
Print 'FK FK__Titleauth__au_id__164452b1 does not exist'

5) How to Rename a Default

Renaming Default 'DF_Orders' to 'DF_Orders_Ord_Date'.
(DF_Orders does not actually exist in the database.)

IF Exists (select * from sysobjects where id = object_id(N'[dbo].[DF_Orders]') and
OBJECTPROPERTY(id, N'IsDefaultCnst') = 1)
Exec sp_rename 'DF_Orders', 'DF_Orders_Ord_Date', 'OBJECT'
IF @@Error <> 0
Raiserror('Failed to rename Default DF_Orders to DF_Orders_Ord_Date',16,1)
Print 'Default DF_Orders Renamed to DF_Orders_Ord_Date'
Print 'Default DF_Orders does not exist'

Another advantage with this type of approach is that the script first checks to see if the Object exists or not before doing any operations on it. So that way, if necessary, you could run the script multiple times on the same server.

Any comments or suggestions are welcome at sumitdig@hotmail.com.

» See All Articles by Columnist Sumit Dhingra

SQL etc Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM