重命名 SQL Server 模式

Question

我该如何使用SQL Server重命名模式（schema）？

- Dawid

Answer 1

如果您在模式中有大量的对象，您可以使用类似以下内容的方法自动生成所有更改（它仅限于表和视图，因此在运行之前，您可能需要将其扩展到存储过程、UDF等）

USE SandBox

DECLARE @OldSchema AS varchar(255)
DECLARE @NewSchema AS varchar(255)
DECLARE @newLine AS varchar(2) = CHAR(13) + CHAR(10)

SET @OldSchema = 'dbo'
SET @NewSchema = 'StackOverflow'

DECLARE @sql AS varchar(MAX)

SET @sql = 'CREATE SCHEMA [' + @NewSchema + ']' + @newLine
SELECT @sql = @sql + 'GO' + @newLine
SELECT @sql = @sql + 'ALTER SCHEMA [' + @NewSchema + '] TRANSFER [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'
     + @newLine
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = @OldSchema

SET @sql = @sql + 'DROP SCHEMA [' + @OldSchema + ']'

PRINT @sql -- NOTE PRINT HAS AN 8000 byte limit - 8000 varchar/4000 nvarchar - see comments
IF (0=1) EXEC (@sql)

- Cade Roux

---

如果您有很多表格，那么这种方法就行不通了，而且 @sql 会超过 varchar(max) 的限制。不幸的是，本地变量也不能声明为 text。 - Rian Schmits

@Rian Schmits，你的模式中有多少个对象？VARCHAR(MAX)的最大存储大小为2^31-1字节（2,147,483,647字节或2GB - 1字节）。这是一个相当大的脚本限制。 - Cade Roux

@Rian Schmits PRINT 语句有 8000 字节的限制。您需要一个自定义的 PRINT SP，将 varchar(MAX) 分成块以打印出来 (http://blog.falafel.com/Blogs/AdamAnderson/07-02-28/T-SQL_Exceeding_the_8000_Byte_Limit_of_the_PRINT_Statement.aspx)。但 EXEC 没有问题。 - Cade Roux

如果旧模式具有扩展属性怎么办？我将您的解决方案适应了tSQLt框架，该框架使用扩展属性来标记“测试类”模式。看看这里。谢谢，Cade！ - Iain Samuel McLean Elder

如果 (0=1) 执行 (@sql) 将始终为假，因此我认为需要从最后一行中删除 IF 部分，否则它将像魅力一样工作!!! 我给你点赞。 - sib10

Answer 2

您可以通过以下方式将单个对象从一个模式移动到另一个模式：

ALTER SCHEMA NewSchema TRANSFER OldSchema.Object;

- CodeMeIt

---

首先，您需要使用“CREATE SCHEMA NewSchema”命令。 - littlegreen

@littlegreen：你可以假设模式已经被创建，因为用户询问如何重命名，而不是创建。 - Phil

@Phil - 不完全是。如果有人创建了OldSchema，并且直觉认为应该重命名而不是创建一个新的并将对象转移到其中，那么不能确定用户知道首先创建新模式。 - Jason Kleban

Answer 3

我将以上两个代码结合起来，并使用游标来避免受到字符串变量大小的限制，逐个执行命令。我假设您已经创建了新模式，并在确认一切正常后删除旧模式。这样更安全... :)

DECLARE @OldSchema AS varchar(255)
DECLARE @NewSchema AS varchar(255)

SET @OldSchema = 'dbo'
SET @NewSchema = 'StackOverflow'

DECLARE @sql AS varchar(MAX)

DECLARE @Schema AS varchar(MAX)
DECLARE @Obj AS varchar(MAX)

-- First transfer Tables and Views

DECLARE CU_OBJS CURSOR FOR
    SELECT TABLE_SCHEMA, TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = @OldSchema

OPEN CU_OBJS

FETCH NEXT FROM CU_OBJS
INTO @Schema, @Obj

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @sql = 'ALTER SCHEMA [' + @NewSchema + '] TRANSFER [' + @OldSchema + '].[' + @Obj + ']'
    PRINT @sql
--  EXEC (@sql)

    FETCH NEXT FROM CU_OBJS
    INTO @Schema, @Obj
END

CLOSE CU_OBJS
DEALLOCATE CU_OBJS


-- Now transfer Stored Procedures

DECLARE CU_OBJS CURSOR FOR
    SELECT sys.schemas.name, sys.procedures.name
    FROM sys.procedures,sys.schemas
    WHERE sys.procedures.schema_id=sys.schemas.schema_id and sys.schemas.name = @OldSchema

OPEN CU_OBJS

FETCH NEXT FROM CU_OBJS
INTO @Schema, @Obj

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @sql = 'ALTER SCHEMA [' + @NewSchema + '] TRANSFER [' + @Schema + '].[' + @Obj + ']'
    PRINT @sql
--  EXEC (@sql)

    FETCH NEXT FROM CU_OBJS
    INTO @Schema, @Obj
END

CLOSE CU_OBJS
DEALLOCATE CU_OBJS

- Adail Retamal

---

我认为你在第一行ALTER语句中颠倒了参数的顺序。@NewSchema 应该在 ALTER SCHEMA 后面首先列出，但你现在把 @OldSchema 放在了最前面。除此之外，它的工作还算不错。它不能深度重命名模式（例如存储过程或视图内部的引用），但这些解决方案也都不能做到。 - Alan McBee

@AlanMcBee 感谢您指出模式交换的问题！曾经让我困扰了一会儿 :) - FreakinOutMan

Answer 4

在SQL Server 2008中重命名拥有多个表的模式的存储过程

   IF OBJECT_ID ( 'dbo.RenameSchema', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.RenameSchema;
   GO                         

CREATE PROCEDURE dbo.RenameSchema               

 @OLDNAME  varchar(500),
@NEWNAME  varchar(500)

AS            
     /*check for oldschema exist or not */
     IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name =  @OLDNAME)

        BEGIN

            RETURN

        END

       /* Create the schema with new name */
      IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = @NEWNAME)

      BEGIN

          EXECUTE( 'CREATE SCHEMA ' + @NEWNAME );

       END                

     /* get the object under the old schema and transfer those objects to new schema */
     DECLARE Schema_Cursor CURSOR FOR

    SELECT ' ALTER SCHEMA ' + @NEWNAME + ' TRANSFER '+ SCHEMA_NAME(SCHEMA_ID)+'.'+ name  
    as ALTSQL from sys.objects WHERE type IN ('U','V','P','Fn') AND 
    SCHEMA_NAME(SCHEMA_ID) = @OLDNAME;

   OPEN Schema_Cursor;          

  DECLARE @SQL varchar(500)         

   FETCH NEXT FROM Schema_Cursor INTO @SQL;

   WHILE @@FETCH_STATUS = 0
    BEGIN
    exec (@SQL) 
    FETCH NEXT FROM Schema_Cursor INTO @SQL;
   END;

   CLOSE Schema_Cursor;

   DEALLOCATE Schema_Cursor;

   /* drop the old schema which should be the user schema */
   IF @OLDNAME <> 'dbo' and  @OLDNAME <> 'guest'
   BEGIN
    EXECUTE ('DROP SCHEMA ' + @OLDNAME) 
    END
   GO

执行重命名模式的过程： 示例：

    EXECUTE RenameSchema 'oldname','newname'
    EXECUTE RenameSchema 'dbo','guest'  

- KESAVAN PURUSOTHAMAN

Answer 5

这是一个简短的版本，但功能良好。

declare @sql varchar(8000), @table varchar(1000), @oldschema varchar(1000), @newschema   varchar(1000)

  set @oldschema = 'old'
  set @newschema = 'dbo'

 while exists(select * from sys.tables where schema_name(schema_id) = @oldschema)

  begin
      select @table = name from sys.tables 
      where object_id in(select min(object_id) from sys.tables where  schema_name(schema_id)  = @oldschema)

    set @sql = 'alter schema [' + @newschema + '] transfer [' + @oldschema + '].[' + @table + ']'

   exec(@sql)
 end

- Tetrapike

---

谢谢！在我的情况下，我正在迁移到一个新的模式，并且必须记得首先执行CREATE SCHEMA [newSchema]！ - Ian Grainger

Answer 6

对于程序

USE DatabaseName

DECLARE @OldSchema AS varchar(255)
DECLARE @NewSchema AS varchar(255)

SET @OldSchema = 'ComputerLearn'
SET @NewSchema = 'Basic'

DECLARE @sql AS varchar(MAX)

SET @sql = 'CREATE SCHEMA [' + @NewSchema + ']' + CHAR(13) + CHAR(10)

SELECT @sql = @sql + 'ALTER SCHEMA [' + @NewSchema + '] TRANSFER [' + sys.schemas.name + '].[' + sys.procedures.name + ']'
     + CHAR(13) + CHAR(10)
FROM sys.procedures,sys.schemas
WHERE sys.procedures.schema_id=sys.schemas.schema_id and sys.schemas.name = @OldSchema

SET @sql = @sql + 'DROP SCHEMA [' + @OldSchema + ']'

PRINT @sql
IF (0=1) EXEC (@sql)

- MSAG

---

如果 (0=1) 执行 (@sql) 将始终为假，因此我认为需要从最后一行中删除 IF 部分，否则它将像魅力一样工作!!! 我给你点赞。 - sib10

Answer 7

最简单的解决方案是：我只有一个dbo架构，其中包含两个表PopulationByCountrySTG和CountryRegionSTG。 (1) 我通过执行以下操作创建了一个新架构:

create schema stg

(2) 我执行了以下命令：

ALTER SCHEMA stg TRANSFER dbo.PopulationByCountrySTG;
ALTER SCHEMA stg TRANSFER dbo.CountryRegionSTG;

完成了。如果对您有用，请告诉我。谢谢大家。

- Kazmi