Scenario:We often face the situation where we need to rename the database. Think about situation, you are working as SQL Server developer for Financial firm and they have the database name TechBrothersIT and they would like to rename to Techbrothers. You need to provide the scripts that can be run in QA, UAT and Production environment.
Solution:Renaming by GUI:
Renaming database is very easy, you can simply Right Click on Database and then choose Rename.
sp_renamedb 'OldDatabaseName','NewDatabaseName' EXEC sp_renamedb 'TechBrothersIT','TechBrothers'
USE master; GO ALTER DATABASE TechBrothersIT Modify Name = TechBrothers ; GO
The database could not be exclusively locked to perform the operation.
USE MASTER GO DECLARE @DatabaseName AS VARCHAR(500) -->Provide the DataBaseName for which want to Kill all processes. SET @DatabaseName='YourDataBaseName' DECLARE @Spid INT DECLARE KillProcessCur CURSOR FOR SELECT spid FROM sys.sysprocesses WHERE DB_NAME(dbid) = @DatabaseName OPEN KillProcessCur FETCH Next FROM KillProcessCur INTO @Spid WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @SQL VARCHAR(500)=NULL SET @SQL='Kill ' + CAST(@Spid AS VARCHAR(5)) EXEC (@SQL) PRINT 'ProcessID =' + CAST(@Spid AS VARCHAR(5)) + ' killed successfull' FETCH Next FROM KillProcessCur INTO @Spid END CLOSE KillProcessCur DEALLOCATE KillProcessCur