Renaming Database Objects in Sql Server

A lot of times we need a change . While working on an existing database,  we may need to change the database name and in some cases want to rename existing database objects. This can be done in a few seconds.
In SQL Server this can be done in this manner :

1. Renaming a database :

         The SQL Server command for renaming a database is :

         Syntax: EXEC sp_renamedb 'oldName', 'newName' ;

        eg:  Suppose we have a database named "GPSTrainees" and we want to rename it to "KLMSTrainees"

  We can write :

        EXEC sp_renamedb 'GPSTrainees' , 'KLMSTrainees' ;

       However, though this command works on SQL Server 2000 and SQL Server 2005, it will not be supported in future versions of SQL Server. The new  command  that  should  be         used  for  SQL Server 2005 and beyond is:

        ALTER DATABASE oldName MODIFY NAME = newName ;
     
        eg: ALTER DATABASE GPSTrainees MODIFY NAME=KLMSTrainees ;

2. Renaming an object :

         For renaming an object like table,stored procedure,triggers we will be using the following  command.

         eg: Suppose we want to rename a table from dipali_Autoincrement to Deepali_Autoincrement
         sp_rename 'dipali_Autoincrement', 'Deepali_Autoincrement', 'OBJECT' ;

Renaming a column :

         Suppose we want to rename a column Full_Name in table dipali_Library  to Name.Then we can write:

         sp_rename 'dbo.dipali_Library.Full_Name', 'Name', 'COLUMN' ;

Renaming Database Owner :

         Change the current database owner to DBadmin
        eg: sp_changedbowner 'DBadmin' ;

When SQL Server Management Studio is used to make  changes,the sp_rename stored procedure is called by the GUI .

When using these commands or the GUI to rename objects, it should be borne in mind that the change is not propagated to dependent objects. These objects cannot use the changed object unless you yourself modify them.  As is apparent, it is pretty easy to rename objects, but it can also have negative effect on your application and database. So before you begin to rename object just make sure that you are aware of impact of the changes.