Babar
15965
0
12/21/2010 (IST)
0
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