重命名 SQL Server 架构
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/316485/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rename SQL Server Schema
提问by
How can I rename a schema using SQL Server?
如何使用 SQL Server 重命名架构？
回答by Cade Roux
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.)
如果模式中有大量对象，则可以使用类似这样的方法自动生成所有更改（它只生成表和视图，因此在运行之前，您可能需要将其扩展为 SP、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)
回答by Ray Lu
You move individual objects from one schema to another via:
您可以通过以下方式将单个对象从一个架构移动到另一个架构：
ALTER SCHEMA NewSchema TRANSFER OldSchema.Object;
回答by Adail Retamal
I have combined both codes above and used cursors to not be limited by the size of the string variables, executing the commands individually. I assume you have already created the new schema and will drop the old one after certifying all is ok. It's safer... :)
我结合了上面的两个代码并使用游标不受字符串变量大小的限制，单独执行命令。我假设您已经创建了新架构，并在证明一切正常后将删除旧架构。更安全... :)
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
回答by KESAVAN PURUSOTHAMAN
The stored procedure to rename the schema which has more tables in SQL server 2008
在 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 the procedure to rename the schema: examples:
执行重命名架构的过程：示例：
EXECUTE RenameSchema 'oldname','newname'
EXECUTE RenameSchema 'dbo','guest'
回答by Tetrapike
This is a short version but works well.
这是一个简短的版本，但效果很好。
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
回答by MSAG
For Procedures
办理手续
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)
回答by Kazmi
Easiest solution that worked for me is:
I have just one schema dbo with two tables PopulationByCountrySTGand CountryRegionSTG
对我有用的最简单的解决方案是：
我只有一个带有两个表的架构 dboPopulationByCountrySTG和CountryRegionSTG
(1) I created a new schema by executing,
(1) 我通过执行创建了一个新模式，
create schema stg
(2) I executed the following commands,
（2）我执行了以下命令，
ALTER SCHEMA stg TRANSFER dbo.PopulationByCountrySTG;
ALTER SCHEMA stg TRANSFER dbo.CountryRegionSTG;
All done. Let me know if it works for you.. Thanks Guys.
全部完成。让我知道它是否适合你..谢谢你们。
