Declare @DBRENAME AS VARCHAR(52)
-- Mention keyword that needs to be added at the end of DB
SET @DBRENAME='_RETIRED' 
DECLARE @DBName AS VARCHAR(52)

DECLARE Cur CURSOR FOR

--List user databaes
  SELECT name from
sys.databases
where database_id>4

OPEN Cur
FETCH Next FROM Cur INTO @DBName
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(@DBName) 
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 @SQLDBRename NVARCHAR(MAX)=NULL
      SET @SQLDBRename='ALTER DATABASE ['+@DBName+'] 
	           SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
      SET @SQLDBRename+=' EXEC sp_renamedb ['+@DBName+'],
          ['+@DBName+@DBRENAME+']'
      SET @SQLDBRename+=' ALTER DATABASE ['+@DBName+@DBRENAME+'] 
      SET MULTI_USER WITH ROLLBACK IMMEDIATE'

         Print @SQLDBRename
         EXEC (@SQLDBRename)
      FETCH Next FROM Cur INTO @DBName
  END
CLOSE Cur
DEALLOCATE Cur

--Get list of Databases
Select @@servername as 'Server', name as DBName 
from sys.databases 
where  database_id>4