How to remove String from Database Name in SQL Server ( Rename Database) - SQL Server Tutorial

Scenario:

Let's say you have renamed databases on SQL Server Instance with "_Test" or you have databases which are created with "-" in the name and you would like to remove "-" or there is space in database name and you would like to remove, You are asked to provide the script to perform any of these tasks.

Solution:

For single database, there is no problem, you can simply use sp_rename. As we would like to check all the databases and rename , we will be using the cursor to rename the databases.

USE MASTER
GO
Declare @RemoveDBPart AS VARCHAR(100)
--Provide What you would like to remove from DB Name
SET @RemoveDBPart='-DEV'
DECLARE @DatabaseName AS VARCHAR(128)

DECLARE Cur CURSOR FOR

--Get list of Databases You want to rename
  SELECT name from
sys.databases
where database_id>4
and name like '%'+@RemoveDBPart+'%'

OPEN Cur
FETCH Next FROM Cur INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
  BEGIN
  
--Innser  Cursor Start
--Kill all user connection in case open for any database
  DECLARE @Spid INT
DECLARE KillProcessCur CURSOR FOR
 SELECT request_session_id
FROM   sys.dm_tran_locks
WHERE  resource_database_id = DB_ID(@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
--Inner Cursor Ends

--Outer Cursor: Rename Database
      DECLARE @SQLRename NVARCHAR(MAX)=NULL
      SET @SQLRename='ALTER DATABASE ['+@DatabaseName+'] 

          SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
      SET @SQLRename+=' EXEC sp_renamedb ['+@DatabaseName+'],

          ['+Replace(@DatabaseName,@RemoveDBPart,'')+']'
      SET @SQLRename+=' ALTER DATABASE ['+

          Replace(@DatabaseName,@RemoveDBPart,'')+'] 
      SET MULTI_USER WITH ROLLBACK IMMEDIATE'

         Print @SQLRename
         EXEC (@SQLRename)
      FETCH Next FROM Cur INTO @DatabaseName
  END
CLOSE Cur
DEALLOCATE Cur

--Get list of Databases
Select name as DBName,state_desc,user_access_desc 
from sys.databases 
where  database_id>4

I had -Test at the end of my databases, I tested above code and it was able to remove -Test , ' '(space), "-" and other characters and it worked.

Note :

Always run scripts in DEV and test. Make required changes if necessary before processed to UAT and Production environments.