CHANGE TABLE NAME / INDEX NAME / COUMN NAME USING A QUERY IN SQL SERVER 2005

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

Advertisements