Saturday, December 7, 2013

Renaming DataBase Object - Sql Server

SQL Server offers  specific tools that allow to rename objects that exist in a SQL Server instance. 
SSMS is management studio to deal all the object in sql server(using SQL Server Management Studio you can also change a database name via the GUI.).but what happen if you did not have access to ssms or you are working through your web  or your application require to rename objects & recreate new object in regular interval of time.
then belows command will helps you  
sp_rename 'db1', 'db2', 'DATABASE';

Suppose you have a php script which id cron at a time to rename & recreate object to shift data.
then no ssms will help you..

Rename database from db1 to db2

sp_renamedb 'db1' , 'db2'or

Rename Object
rename an existing table, stored procedure, trigger, etc..  A
 rename object object1 to object2.

sp_rename 'object1', 'object2', 'OBJECT';
sp_rename 'object1', 'object2'

Rename an Index
rename an index from index1 to IX_index1
sp_rename 'index1 ', 'IX_index1', 'INDEX';

Change the current database owner to new_admin
sp_changedbowner 'new_admin'


sp_rename 'obejctname1','objectname2'

as all object name in sql server are unique by name , 
 tablename , sp name,trigger name etc can not be same