SQL "How To's" Part 4

November 2, 2001

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)
BEGIN
Exec sp_rename 'Sales', 'Orders'

IF @@Error <> 0
Raiserror('Failed to rename Table Sales to Orders',16,1)
ELSE
Print 'Table Sales Renamed to Orders'
END
ELSE
Print 'Table Sales does not exist'
GO

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
BEGIN
Exec sp_rename 'Orders.[Qty]', 'Quantity', 'COLUMN'

IF @@Error <> 0
Raiserror('Failed to rename column Qty to Quantity',16,1)
ELSE
Print 'Column Qty Renamed to Quantity'
END
ELSE
Print 'Column Quantity already exists'
GO

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)
BEGIN
Exec sp_rename 'UPKCL_sales', 'PK_Orders', 'OBJECT'

IF @@Error <> 0
Raiserror('Failed to rename PK UPKCL_sales to PK_Orders',16,1)
ELSE
Print 'PK UPKCL_sales Renamed to PK_Orders'
END
ELSE
Print 'PK UPKCL_sales does not exist'
GO

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)
BEGIN
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)
ELSE
Print 'FK FK__Titleauth__au_id__164452b1 Renamed to FK_Titleauthor_Author_auid'
END
ELSE
Print 'FK FK__Titleauth__au_id__164452b1 does not exist'
GO

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)
BEGIN
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)
ELSE
Print 'Default DF_Orders Renamed to DF_Orders_Ord_Date'
END
ELSE
Print 'Default DF_Orders does not exist'
GO

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 "How To's" Part 4

Whitepapers and eBooks

Webcasts

Downloads and eKits

Tutorials and Demos