Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Tuesday, 30 April 2013

How to Change table name or column name in Sqlserver 2005/2008/2012

How to Change table name or column name in Sqlserver 2005/2008
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......!