T-SQL (SQL SERVER) - Rename Database Error Msg

Question

I'm trying to rename my database but I'm not familiar with the syntax. I've tried 2 different things but get the following error msgs...

Method 1:

DECLARE @old varchar(100)
DECLARE @new varchar(100)
SET @old = 'ver.1'
SET @new = 'ver.2'

DECLARE @query varchar(max)
set @query = 'ALTER DATABASE @old MODIFY NAME = @new'
exec @query

Error: Could not find stored procedure 'ALTER DATABASE @old MODIFY NAME = @new'.

Method 2:

DECLARE @old varchar(100)
DECLARE @new varchar(100)
SET @old = 'ver.1'
SET @new = 'ver.2'

ALTER DATABASE @old MODIFY NAME = @new

Error: Incorrect syntax near @old

Community · Accepted Answer · 2020-07-29 18:40:42Z

1

Stop using exec for dynamic SQL; use sp_executesql. Also, you can't inline parameterize arguments to ALTER DATABASE that way - you need to use string concatenation. Finally, because you chose to use illegal characters in your database name (.), you need to escape these with QUOTENAME.

DECLARE @old varchar(100) = 'ver.1';
DECLARE @new varchar(100) = 'ver.2';

DECLARE @query NVARCHAR(MAX) = N'ALTER DATABASE ' 
  + QUOTENAME(@old) + ' MODIFY NAME = ' + QUOTENAME(@new) + ';';

EXEC sp_executesql @query;

edited Jul 29, 2020 at 18:40

CommunityBot

11 silver badge

answered Nov 21, 2013 at 23:01

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

chaitanya.moguluri · Accepted Answer · 2013-11-29 08:39:55Z

0

We can also use sp_renamedb to rename database.

EXEC sp_renamedb 'old_name' , 'new_name'

answered Nov 29, 2013 at 8:39

chaitanya.moguluri

2402 silver badges8 bronze badges

Collectives™ on Stack Overflow

T-SQL (SQL SERVER) - Rename Database Error Msg

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related