Rename Database in SQL Server

set nocount on;
go

print '- Using ''master'' database.';
use master;
go

print '- Checking for existence of ''usp_rename_database'' procedure...';
if (select OBJECT_ID('usp_rename_database', 'P')) is not null
begin
    print '  >> Procedure already exists... dropping it!';
    drop proc usp_rename_database;
end;
go

create procedure usp_rename_database
    @DatabaseName varchar(100),
    @NewName varchar(100)
as
begin
    declare @sql varchar(1000);
    declare @physLoc varchar(1000), @physLoc_log varchar(1000);
    declare @physLocNew varchar(1000), @physLoc_logNew varchar(1000);

    if @DatabaseName not in ('master', 'DBA', 'msdb', 'tempdb')
    begin
        if exists (select * from sys.databases where Name = @DatabaseName)
        begin
            select @physLoc = physical_name
            from sys.master_files
            where name = @DatabaseName and type_desc = 'ROWS';

            select @physLoc_log = physical_name
            from sys.master_files
            where name = @DatabaseName+'_log' and type_desc = 'LOG';

            --// SET TO SINGLE USER
            set @sql = 'ALTER DATABASE '+@DatabaseName+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE';
            execute (@sql);

            --// RENAME DATABASE: LOGICAL NAME
            set @sql = '
                ALTER DATABASE ['+@DatabaseName+']
                MODIFY FILE  (NAME=N'''+@DatabaseName+''', NEWNAME=N'''+@NewName+''')
                ALTER DATABASE ['+@DatabaseName+']
                MODIFY FILE  (NAME=N'''+@DatabaseName+'_log'', NEWNAME=N'''+@NewName+'_log'')';
            execute (@sql);

            set @sql = 'ALTER DATABASE '+@DatabaseName+' SET OFFLINE';
            execute (@sql);

            exec sp_configure
                'xp_cmdshell',
                1; --// 0 = Disabled , 1 = Enabled
            reconfigure with override;

            --// RENAME DATABASE: PHYSICAL FILE NAME
            set @sql = 'EXEC xp_cmdshell ''RENAME "'+@physLoc+'", "'+@newName+'.mdf"'''; -- CurrentPath&Name, NewName
            execute (@sql);

            set @sql = 'EXEC xp_cmdshell ''RENAME "'+@physLoc_log+'", "'+@newName+'_log.ldf"'''; -- CurrentPath&Name, NewName
            execute (@sql);

            exec sp_configure
                'xp_cmdshell',
                0; --// 0 = Disabled , 1 = Enabled
            reconfigure with override;

            ----// ATTACH (CREATE) DATABASE
            select @physLocNew = REPLACE(@physLoc, @DatabaseName, @newName);
            select @physLoc_logNew = REPLACE(@physLoc_log, @DatabaseName, @newName);

            set @sql = '
            CREATE DATABASE ['+@NewName+'] ON
            ( FILENAME = N'''+@physLocNew+''' ),
            ( FILENAME = N'''+@physLoc_logNew+''' )
            FOR ATTACH ';
            execute (@sql);

            --// DETACH DATABASE
            set @sql = 'EXEC master.dbo.sp_detach_db @dbname = N'''+@DatabaseName+'''';
            execute (@sql);
        end;
    end;
end;
go

print '- ''usp_rename_database'' procedure created.';
go

--
-- Mark the proc as a system object to allow it to be called transparently from
-- other databases:
exec sp_MS_marksystemobject usp_rename_database;
go

--
-- print 'Granting EXECUTE permission on usp_rename_database to all users';
-- grant exec on usp_rename_database TO public;

set nocount off;
go

print '';
print 'Finished.';