1.sp_rename changes the name of a user-created object in the current database.
2.Object can be a table, index, column.
Examples are given below.
RENAMING A TABLE
EXEC sp_rename ‘Employee’ , ‘NewEmployee’
GO
OUTPUT
______
Caution: Changing any part of an object name could break scripts and stored procedures.
–Check the new table
SELECT * FROM NewEmployee
OUTPUT
________
2 Gaurav 80000
3 Saurabh 20000
5 Neelam 18000
6 Saurabh 24000
_________________________________________________________________________________________
_________________________________________________________________________________________
RENAMING A COLUMN
EXEC sp_rename ‘NewEmployee.EMPNAME’, ‘NEWEMPNAME’, ‘COLUMN’;
OUTPUT
______
Caution: Changing any part of an object name could break scripts and stored procedures.
–Check the new column name in the table definition
SELECT * FROM NewEmployee
Sp_help NewEmployee
OUTPUT
______
id int no 4 10 0 no (n/a) (n/a) NULL
NEWEMPNAME varchar no 100 yes no yes SQL_Latin1_General_CP1_CI_AS
SALARY decimal no 9 18 0 yes (n/a) (n/a) NULL
_________________________________________________________________________________________
_________________________________________________________________________________________
RENAMING AN INDEX
CREATE an index first
CREATE NONCLUSTERED INDEX MyIndex ON NewEmployee (NEWEMPNAME ASC );
GO
–Now change the name of the index
EXEC sp_rename ‘NewEmployee.MyIndex’, ‘MynewIndex’, ‘INDEX’;
GO
OUTPUT
________
Caution: Changing any part of an object name could break scripts and stored procedures.
Sp_help NewEmployee
OUTPUT for indexes only
_____
MynewIndex nonclustered located on PRIMARY NEWEMPNAME
PK__EMPLOYEE__2DA7A64D clustered, unique, primary key located on PRIMARY id
_________________________________________________________________________________________
_________________________________________________________________________________________
Pawan Kumar
Pawankkmr@hotmail.com
Improving my SQL BI Skills 