How to Rename all the databases on SQL Server - SQL Server Tutorial

Scenario:

You are working as DBA or Developer and you need to prepare script that should rename the existing databases. You might be interested to add "_Backup" or "_Test" or "_TodayDate" or anything you like to append to existing databases. Below Code can be used to rename existing databases on SQL Server Instance. You can also change the select query for the cursor to choose only required databases instead of selecting all the databases.


Solution:


Let's say if I am interested to add "_Test" with my all databases, I can use below script.

USE MASTER
GO
Declare @DBPart AS VARCHAR(100)
--Provide the Part you like to add to DBs
SET @DBPart='_Test'
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

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+'],
          ['+@DatabaseName+@DBPart+']'
      SET @SQLRename+=' ALTER DATABASE ['+@DatabaseName+@DBPart+'] 
      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 executed above query and it was able to rename all the user databases on my SQL Server Instance.
How to rename all the databases in SQL Server Instance - SQL Server Tutorial

1 comment:

  1. this may be good for adding some thing in the database name, but what for removing a particular prefix from all the databases names at once. ??

    like if we want to remove "test_" from all the database like "test_databasename"? how to do that?

    ReplyDelete