重命名 SQL Server 架构 [英] Rename SQL Server Schema

问题描述

如何使用 SQL Server 重命名架构?

How can I rename a schema using SQL Server?

推荐答案

如果你在一个模式中有大量的对象，你可以使用这样的东西来自动生成所有的变化(它只对表和视图产生影响，所以在运行之前，您可能需要将其扩展为 SP、UDF 等)

If you have a large number of objects in a schema, you can use something like this to generate all the changes automatically (it only does tables and views, so before you run it, you might need to expand it to SPs, UDFs, etc.)

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)

这篇关于重命名 SQL Server 架构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！