CODEWITHSUNDEEP
sqlsql-servert-sql
goofyui
goofyui

Reputation: 3492

How to modify / rename the database name in SQL?

How to modify / rename the database name in SQL?

Running the below script throws exception as " Incorrect syntax near @dbname "

Declare @dbname nvarchar(50)
set @dbname = 'MyDatabase_Test'

ALTER DATABASE MyDatabase MODIFY NAME = @dbname;

Upvotes: 2

Views: 8372

Answers (4)

JacNet
JacNet

Reputation: 9

On MS SQL Server

USE master;
GO

DECLARE @dbNewName NVARCHAR(50) = 'db_NewName'

-- take the db to single user mode
ALTER DATABASE db_OldName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- rename the database; NVARCHAR variable for use with SP_SQLEXEC
DECLARE @stmt NVARCHAR(100) = 'ALTER DATABASE db_OldName MODIFY NAME = ' + @dbNewName
EXEC SP_SQLEXEC @stmt;

-- bring the renamed db out of single user mode; to be available to everyone
ALTER DATABASE db_NewName SET MULTI_USER;
GO

Tested on SQL Server 2016.

Upvotes: 0

khaleel rashid
khaleel rashid

Reputation: 11

USE master;
GO
ALTER DATABASE MyTestDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE MyTestDatabase MODIFY NAME = MyTestDatabaseCopy;
GO
ALTER DATABASE MyTestDatabaseCopy SET MULTI_USER;
GO

Upvotes: 1

Emilio Lucas Ceroleni
Emilio Lucas Ceroleni

Reputation: 1598

DECLARE @SQLString nvarchar(500);  
DECLARE @OldDbName nvarchar(100);
DECLARE @NewDbName nvarchar(100);

SET @OldDbName =  'MyTestDatabase';
SET @NewDbName =  'MyNewTestDatabase';
BEGIN TRY

        SET @SQLString =  
                    N'ALTER DATABASE ' +  @OldDbName + ' SET SINGLE_USER WITH ROLLBACK 
IMMEDIATE'; 
        EXECUTE sp_executesql @SQLString

        SET @SQLString =  
                    N'ALTER DATABASE ' +  @OldDbName + ' MODIFY NAME = ' + @NewDbName;
        EXECUTE sp_executesql @SQLString            

        SET @SQLString =  
                    N'ALTER DATABASE ' +  @NewDbName + ' SET MULTI_USER' ;
        EXECUTE sp_executesql @SQLString

END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE();

END CATCH

Upvotes: 5

Joe Stefanelli
Joe Stefanelli

Reputation: 135868

You have to do this as dynamic SQL.

Declare @dbname nvarchar(50)
set @dbname = 'MyDatabase_Test'

EXEC('ALTER DATABASE MyDatabase MODIFY NAME = ' + @dbname);

Upvotes: 4

Related Questions