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......!