SQL “How To’s” Part 4



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 [email protected].


»


See All Articles by Columnist
Sumit Dhingra

Previous articleScript Disclaimer
Next articlegrant_all_to_role.sql

Latest Articles