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
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. ??
ReplyDeletelike if we want to remove "test_" from all the database like "test_databasename"? how to do that?