在 SQL 中更改表的架构名称
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15482838/
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
Change Schema Name Of Table In SQL
提问by theChampion
I want to change schema name of table Employeesin Database. In the current table Employeesdatabase schema name is dboI want to change it to exe. How can I do it ?
我想更改Employees数据库中表的架构名称。在当前表Employees数据库模式名称中，dbo我想将其更改为exe. 我该怎么做 ？
Example:
例子：
FROM
从
dbo.Employees
TO
到
exe.Employees
I tried with this query:
我试过这个查询：
ALTER SCHEMA exe TRANSFER dbo.Employees
But this gives me an error:
但这给了我一个错误：
Cannot alter the schema 'exe', because it does not exist or you do not have permission.
无法更改架构“exe”，因为它不存在或您没有权限。
What did I miss?
我错过了什么？
回答by Pandian
Create Schema :
创建架构：
IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'exe'))
BEGIN
EXEC ('CREATE SCHEMA [exe] AUTHORIZATION [dbo]')
END
ALTER Schema :
更改架构：
ALTER SCHEMA exe
TRANSFER dbo.Employees
回答by DevelopmentIsMyPassion
Try below
试试下面
declare @sql varchar(8000), @table varchar(1000), @oldschema varchar(1000), @newschema varchar(1000)
set @oldschema = 'dbo'
set @newschema = 'exe'
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 Lauren
ALTER SCHEMA NewSchema TRANSFER [OldSchema].[TableName]
I always have to use the brackets when I use the ALTER SCHEMAquery in SQL, or I get an error message.
当我ALTER SCHEMA在 SQL 中使用查询时，我总是必须使用括号，否则我会收到错误消息。
回答by Jason D.
Through SSMS, I created a new schema by:
通过 SSMS，我通过以下方式创建了一个新架构：
- Clicking the Security folder in the Object Explorer within my server,
- right clicked Schemas
- Selected "New Schema..."
- Named my new schema (exe in your case)
- Hit OK
- 单击我服务器中对象资源管理器中的安全文件夹，
- 右键单击架构
- 选择“新架构...”
- 命名我的新架构（在您的情况下为 exe）
- 点击确定
I found this post to change the schema, but was also getting the same permissions error when trying to change to the new schema. I have several databases listed in my SSMS, so I just tried specifying the database and it worked:
我发现这篇文章更改了架构，但在尝试更改为新架构时也遇到了相同的权限错误。我的 SSMS 中列出了几个数据库，所以我只是尝试指定数据库并且它有效：
USE (yourservername)
ALTER SCHEMA exe TRANSFER dbo.Employees
回答by user2599599
Your Code is:
您的代码是：
FROM
dbo.Employees
TO
exe.Employees
I tried with this query.
我试过这个查询。
ALTER SCHEMA exe TRANSFER dbo.Employees
Just write create schema exeand execute it
只需编写create schema exe并执行它
回答by Dilip Kr Singh
CREATE SCHEMA exe AUTHORIZATION [dbo]
GO
ALTER SCHEMA exe
TRANSFER dbo.Employees
GO
回答by Chains
Check out MSDN...
看看 MSDN...
CREATE SCHEMA: http://msdn.microsoft.com/en-us/library/ms189462.aspx
CREATE SCHEMA：http: //msdn.microsoft.com/en-us/library/ms189462.aspx
Then
然后
ALTER SCHEMA: http://msdn.microsoft.com/en-us/library/ms173423.aspx
ALTER SCHEMA：http: //msdn.microsoft.com/en-us/library/ms173423.aspx
Or you can check it on on SO...
或者你可以在 SO 上检查它...
回答by djangojazz
Be very very careful renaming objects in sql. You can cause dependencies to fail if you are not fully away with what you are doing. Having said that this works easily(too much so) for renaming things provided you have access proper on the environment:
在 sql 中重命名对象时要非常小心。如果您没有完全放弃正在做的事情，可能会导致依赖关系失败。话虽如此，这对于重命名事物来说很容易（太多了），前提是您可以在环境中正确访问：
exec sp_rename 'Nameofobject', 'ReNameofobject'
回答by billmack30
Make sure you're in the right database context in SSMS. Got the same error as you, but I knew the schema already existed. Didn't realize I was in 'MASTER' context. ALTER worked after I changed context to my database.
确保您在 SSMS 中处于正确的数据库上下文中。和你有同样的错误，但我知道模式已经存在。没有意识到我处于“主”上下文中。在我将上下文更改为我的数据库后，ALTER 起作用了。
回答by iravinandan
In case, someone looking for lower version -
以防万一，有人在寻找较低版本 -
For SQL Server 2000:
对于 SQL Server 2000：
sp_changeobjectowner @objname = 'dbo.Employess' , @newowner ='exe'
sp_changeobjectowner @objname = 'dbo.Employess' , @newowner ='exe'
