• Now Online : 33
  • admin@codemyne.net

Introduction

sp_rename is used to change the user created objects in a database. This object can be a table, index, column, alias data type, or Microsoft .NET Framework common language runtime (CLR) user-defined type.

sp_rename automatically renames the associated index whenever a PRIMARY KEY or UNIQUE constraint is renamed. If a renamed index is tied to a PRIMARY KEY constraint, the PRIMARY KEY constraint is also automatically renamed by sp_rename.

Remember: Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view. So this is not recommended to rename stored procedures, triggers, user-defined functions, or views; instead, drop the object and re-create it with the new name.

Syntax for sp_rename: sp_rename [objectname] [newname] [objecttype]

Example 1: Renaming Table

USE AdventureWorks; GO
EXEC sp_rename 'Sales.SalesTerritory', 'SalesTerr';
GO

Example 2: Renaming a column

USE AdventureWorks;
GO
EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';
GO

Example 3: Renaming an Index

USE AdventureWorks; 
GO
EXEC sp_rename N'Purchasing.ProductVendor.IX_ProductVendor_VendorID', N'IX_VendorID', N'INDEX';
GO 

Note: if you rename a table column and that column is referenced in a trigger, the trigger must be modified to reflect the new column name.

Comments/Suggestions are invited. Happy coding......!

Comments Post a Comment