如何在SQL中修改/重命名数据库名称？执行指定脚本报错求助

Hey there, let's break down why your script is throwing that error and how to fix it properly.

Why the Error Happens #

The core issue here is that SQL Server doesn’t allow using local variables directly with the ALTER DATABASE MODIFY NAME syntax. That statement expects a literal database name (a constant value) in the MODIFY NAME = position, so passing @dbname triggers the syntax error you’re seeing.

The Correct Approach: Dynamic SQL #

To use a variable for the new database name, you need to build and execute a dynamic SQL statement. This lets you inject the variable's value into the SQL string before execution. Here's a safe, working version of your script:

DECLARE @dbname NVARCHAR(50)
SET @dbname = 'MyDatabase_Test'
DECLARE @sql NVARCHAR(MAX)

-- Build the dynamic SQL, using QUOTENAME to handle special characters/spaces
SET @sql = N'ALTER DATABASE MyDatabase MODIFY NAME = ' + QUOTENAME(@dbname) + ';'

-- Execute the constructed SQL
EXEC sp_executesql @sql

Key Additional Tips:

• QUOTENAME() wraps the database name in square brackets, which handles cases where your new name might include spaces, reserved words, or special characters. It also helps prevent SQL injection risks, even if you’re using a hardcoded variable here.
• Before renaming, make sure no other users are connected to the target database—active connections can cause the rename to fail. You can temporarily set the database to single-user mode first:

  -- Switch to single-user mode (rolls back active transactions immediately)
  ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  
  -- Run the rename script here
  
  -- Switch back to multi-user mode
  ALTER DATABASE MyDatabase_Test SET MULTI_USER;
  

• You’ll need sufficient permissions to rename the database: either the ALTER ANY DATABASE server-level permission, or ownership of the target database.

内容的提问来源于stack exchange，提问作者goofyui