sas rename重命名_如何使用sp_rename命令在SQL Server中重命名表

sas rename重命名

In this article, we will learn to rename tables in SQL Server. sp_rename is a built-in stored procedure which helps to rename user tables in the SQL Server. During the software development life cycle, we might be faced with various scenarios that required to rename the existing tables in the database. For example, the tables which are created in the SQL Server might be needed to be renamed, because of the software version upgrades, bug fixing or other various development requirements. These and similar reasons can make renaming tables important. So, in this article, we will particularly focus on how to rename the tables in SQL Server.

在本文中，我们将学习在SQL Server中重命名表。 sp_rename是一个内置存储过程，可帮助重命名SQL Server中的用户表。 在软件开发生命周期中，我们可能会遇到各种需要重命名数据库中现有表的场景。 例如，由于软件版本升级，错误修复或其他各种开发要求，可能需要重命名在SQL Server中创建的表。 这些和类似的原因会使重命名表很重要。 因此，在本文中，我们将特别关注如何在SQL Server中重命名表。

如何在SQL Server中重命名表 (How to rename a table in SQL Server)

The sp_rename is a stored procedure which helps to rename tables in SQL Server and the usage syntax will be like the below:

sp_rename是一个存储过程，可帮助重命名SQL Server中的表，用法语法如下所示：

sp_rename 'old_table_name', 'new_table_name'

The result outputs of this procedure might be 0 or non-zero values. 0 value indicates that the procedure execution successfully completed and non-zero values indicate failure. Now, let’s create a sample table and then rename this table name:

此过程的结果输出可能是0或非零值。 0值表示过程执行成功完成，非零值表示失败。 现在，让我们创建一个示例表，然后重命名该表名称：

DROP TABLE IF EXISTS TestOrderList;
DROP TABLE IF EXISTS NewTestOrderList
GO
CREATE TABLE TestOrderList
( 
Id INT PRIMARY KEY IDENTITY(1, 1), 
OrderCode VARCHAR(100), 
OrderName VARCHAR(100), DueDate DATETIME
)
INSERT INTO  TestOrderList
VALUES
( 'X120', 'Notebook', '20191212' ), 
( 'W201', 'PC', '20191120' ), 
( 'KWW901', 'Printer', '20191001' )
GO 
SELECT * FROM TestOrderList

As we can see TestOrderList table storing some data, however, renaming the table does not influence the TestOrderList data only it makes modification in the name of the tables. The following query renames the TestOrderList table to NewTestOrderList:.

如我们所见， TestOrderList表存储了一些数据，但是，重命名该表不会影响TestOrderList数据，只是会对其表名进行修改。 以下查询将TestOrderList表重命名为NewTestOrderList ：。

sp_rename 'dbo.TestOrderList' ,'NewTestOrderList'

After the execution of the sp_rename procedure, it generates a warning message like the below one:

在执行sp_rename过程之后，它会生成如下警告消息：

Caution: Changing any part of an object name could break scripts and stored procedures.”

注意：更改对象名称的任何部分都可能会破坏脚本和存储过程。”

The purpose of this message is to notify about that if you rename a table, you have to change the old table name with the new one in the database objects (stored procedure, view, etc), which include the old table name. Thus, we can prevent errors that can be caused by renamed tables. If we want to work with the return value, we can execute the table below. Before executing the following query, re-run the sample table creation script.

该消息的目的是通知您，如果您重命名表，则必须在数据库对象（存储过程，视图等）中使用新表名更改旧表名，其中包括旧表名。 因此，我们可以防止重命名表可能导致的错误。 如果要使用返回值，可以执行下表。 在执行以下查询之前，请重新运行示例表创建脚本。

DECLARE @RetVal AS INT
EXEC @RetVal=sp_rename 'dbo.TestOrderList', 'NewTestOrderList'
SELECT @RetVal AS ReturnValue

如何使用SSMS重命名SQL Server中的表 (How to rename a table in SQL Server with SSMS)

In order to rename a table, we can follow another method procedure through the SQL Server Management Studio, which is different than the sp_rename option. We can find out the table name whose name will change under the Tables path of the SSMS and then click the Rename in the menu or we can press to F2 function key as a shortcut.

为了重命名表，我们可以通过SQL Server Management Studio遵循另一个方法过程，该过程不同于sp_rename选项。 我们可以在SSMS的Tables路径下找到要更改其名称的表名，然后单击菜单中的Rename或按F2功能键作为快捷方式。

After this step, the table name will be changed as an editable and then we will give the new table name and press Enter.

完成此步骤后，表名将被更改为可编辑状态，然后我们将给出新的表名并按Enter 。

Answer the message box question as Yes and complete the table renaming.

将消息框问题回答为“ 是”，然后完成表重命名。

如何在SQL Server中重命名临时表名称 (How to rename a temporary table name in SQL Server)

SQL Server allows creating temporary tables and temporary tables provide to store data in it. However, we can not use the sp_rename to rename the temporary tables. Now, let’s prove this case and find out the reason. When we try to execute the following query, we will get an error like this:

SQL Server允许创建临时表，临时表提供了在其中存储数据的功能。 但是，我们不能使用sp_rename重命名临时表。 现在，让我们证明这种情况并找出原因。 当我们尝试执行以下查询时，我们将收到如下错误：

DROP TABLE IF EXISTS  #UserTable
  GO
CREATE TABLE #UserTable(UserId INT PRIMARY KEY IDENTITY(1,1), UserName VARCHAR(100),UserLoginDate DATETIME)
   
EXEC sp_rename 'dbo.#UserTable' ,  '#NewUserTable'

“An invalid parameter or option was specified for procedure ‘sys.sp_rename’.” Actually, the reason for error details is hidden in the procedure creation script depths. In order to figure out the details of this issue about the temporary table renaming, we should analyze the creation script. Now, let’s apply the following steps and solve the mystery.

“为过程'sys.sp_rename'指定了无效的参数或选项。” 实际上，错误详细信息的原因隐藏在过程创建脚本的深度中。 为了找出有关临时表重命名的问题的详细信息，我们应该分析创建脚本。 现在，让我们应用以下步骤并解开谜团。

At first, we will run the following query in order to obtain the creation script of the sp_rename.

首先，我们将运行以下查询以获得sp_rename的创建脚本。

EXEC sp_helptext 'sp_rename'

After that, we will paste and copy the result of a new query window and focus on PHASE 32 part of the script.

之后，我们将粘贴并复制新查询窗口的结果，并着重于脚本的PHASE 32部分。

PHASE 32 section identifies the reason for the error, obviously. If the old table name or the new table name parameter first character is equal to # sign it returns an error. Therefore, we can not rename the temp tables. So, how can we overcome this issue? The following query offers an alternative method to rename the temporary tables.

显然， 阶段32部分确定了错误的原因。 如果旧表名或新表名参数的第一个字符等于＃号，则返回错误。 因此，我们不能重命名临时表。 那么，我们如何克服这个问题呢？ 以下查询提供了一种重命名临时表的替代方法。

DROP TABLE IF EXISTS  #UserTable
  GO
CREATE TABLE #UserTable(UserId INT PRIMARY KEY IDENTITY(1,1), UserName VARCHAR(100),UserLoginDate DATETIME)
INSERT INTO #UserTable 
VALUES 
('TestUser','20190302'),
('QAUser','20190406'),
('ProdUser','20190503')
SELECT * FROM #UserTable 
DROP TABLE IF EXISTS #NewUserTable
SELECT * INTO #NewUserTable FROM  #UserTable
DROP TABLE #UserTable
SELECT * FROM #NewUserTable

As we can see, we renamed the temporary tables in an alternative method, but this method is not suitable for the user tables if they contain a big amount of data and other considerations.

如我们所见，我们用另一种方法对临时表进行了重命名，但是如果用户表包含大量数据和其他注意事项，则该方法不适用于用户表。

表重命名和锁定 (Table renaming and locking)

In some cases, we required to execute the DDL (data definition language) statements in an explicit transaction. Starting from this point we can execute sp_rename like the below in an explicit transaction:

在某些情况下，我们需要在显式事务中执行DDL（数据定义语言）语句。 从这一点开始，我们可以在一个显式事务中像下面那样执行sp_rename ：

BEGIN TRAN
 
EXECUTE sp_rename 'old_table_name', 'new_table_name'
        
END TRAN

However, the renamed table acquires schema modification lock (SCH-M) during the renaming transaction, for this reason, other sessions will wait until the completion of this transaction. SCH-M acquired by sessions when altering the metadata of the objects. (For more details about the DDL statements locking issue, you can see the SQL Server lock issues when using a DDL (including SELECT INTO) clause in long-running transactions article). Now, we will reinforce this scenario with the following example.

但是，重命名的表在重命名事务期间会获得架构修改锁（SCH-M），因此，其他会话将等待此事务完成。 会话在更改对象的元数据时获取的SCH-M。 （有关DDL语句锁定问题的更多详细信息，您可以在长期运行的事务中看到使用DDL（包括SELECT INTO）子句时的SQL Server锁定问题 ）。 现在，我们将通过以下示例来加强这种情况。

In this step, we will create a sample table and then we will rename it in a explicit transaction:

在此步骤中，我们将创建一个示例表，然后在一个显式事务中将其重命名：

DROP TABLE IF EXISTS Test_UserList;
GO
CREATE TABLE Test_UserList
( 
Id INT PRIMARY KEY IDENTITY(1, 1), 
UserCode VARCHAR(100), 
UserName VARCHAR(100)
)
INSERT INTO Test_UserList
VALUES
( 'XK04', 'Power User'), 
( 'KK12', 'Admin' ), 
( 'PL56', 'A' )

In the following query, the table renaming operation will be executed in a transaction block and then the Test_UserList table will be acquired SCH-M lock.

在以下查询中，将在事务块中执行表重命名操作，然后将获取Test_UserList表SCH-M锁。

BEGIN TRANSACTION
EXEC sp_rename 'dbo.OrderList' ,'NewOrderList'
WAITFOR DELAY '00:01'
COMMIT TRANSACTION

Now we will open another query window and execute the following query. so that we are able to figure out the lock type which is created by the table renaming session.

现在，我们将打开另一个查询窗口并执行以下查询。 这样我们就可以找出由表重命名会话创建的锁类型。

SELECT dm_tran_locks.request_session_id,
        dm_tran_locks.resource_database_id,
        DB_NAME(dm_tran_locks.resource_database_id) AS dbname,
        CASE
            WHEN resource_type = 'OBJECT'
                THEN (
                SELECT NAME  
                FROM sys.objects WITH(NOLOCK) WHERE object_id = dm_tran_locks.resource_associated_entity_id
                )
               
            ELSE            (
            SELECT NAME  FROM
            sys.objects WITH(NOLOCK) WHERE object_id = partitions.OBJECT_ID
            ) 
     
        END AS ObjectName,
        partitions.index_id,
 
        dm_tran_locks.resource_type,
        dm_tran_locks.resource_description,
        dm_tran_locks.resource_associated_entity_id,
        dm_tran_locks.request_mode,
        dm_tran_locks.request_status
FROM sys.dm_tran_locks 
LEFT JOIN sys.partitions  ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id
 
WHERE resource_associated_entity_id > 0
   AND resource_database_id = DB_ID()
 
ORDER BY request_session_id, resource_associated_entity_id

As we can see in the above illustration showed, the NewOrderList table acquired Sch-M lock.

如上图所示， NewOrderList表获得了Sch-M锁。

结论 ( Conclusion)

In this article, we learned how to rename tables with the help of the sp_rename procedure with different aspects in SQL Server. This procedure is very helpful and pretty simple to rename tables.

在本文中，我们学习了如何在SQL Server的不同方面借助sp_rename过程重命名表。 此过程非常有用，并且重命名表非常简单。

翻译自: https://www.sqlshack.com/how-to-rename-tables-in-sql-server-with-the-sp_rename-command/

sas rename重命名