blocks|key|194297|text|创建架构:|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|194298|IF+(NOT+EXISTS+(SELECT+*+FROM+sys.schemas+WHERE+name+=+'exe'))+
BEGIN
++++EXEC+('CREATE+SCHEMA+[exe]+AUTHORIZATION+[dbo]')
END|code-block|syntax|javascript|194299|ALTER+Schema+:|194300|ALTER+SCHEMA+exe+
++++TRANSFER+dbo.Employees|194301|entityMap^0|0|5|0|0|0|E|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|Y|8|@]|D|@]|E|$]]]|P|$]]

Create Schema :

<pre><code>IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'exe')) 
BEGIN
 EXEC ('CREATE SCHEMA [exe] AUTHORIZATION [dbo]')
END
</code></pre>

ALTER Schema : 

<pre><code>ALTER SCHEMA exe 
 TRANSFER dbo.Employees
</code></pre>

blocks|key|194357|text|ALTER+SCHEMA+NewSchema+TRANSFER+[OldSchema].[TableName]|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|194358|当我在SQL中使用ALTER+SCHEMA查询时，我总是必须使用括号，否则我会收到一条错误消息。|unstyled|offset|length|style|CODE|194359|entityMap^0|0|9|C|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|N|8|@$G|O|H|P|I|J]]|9|@]|A|$]]|$1|K|3|-4|5|F|7|Q|8|@]|9|@]|A|$]]]|L|$]]

<pre><code>ALTER SCHEMA NewSchema TRANSFER [OldSchema].[TableName]
</code></pre>

I always have to use the brackets when I use the <code>ALTER SCHEMA</code> query in SQL, or I get an error message.

blocks|key|194293|text|请在下面尝试|type|unstyled|depth|inlineStyleRanges|entityRanges|data|194294|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+'+%2B+@newschema+%2B+'+transfer+'+%2B+@oldschema+%2B+'.'+%2B+@table

+++exec(@sql)
+end|code-block|syntax|javascript|194295|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try below

<pre><code>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
</code></pre>

blocks|key|3031599|text|CREATE+SCHEMA+exe+AUTHORIZATION+[dbo]
GO

ALTER+SCHEMA+exe
TRANSFER+dbo.Employees
GO|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3031600|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>CREATE SCHEMA exe AUTHORIZATION [dbo]
GO

ALTER SCHEMA exe
TRANSFER dbo.Employees
GO
</code></pre>

blocks|key|3031565|text|通过SSMS，我通过以下方式创建了一个新的模式：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3031566|3031567|在我的服务器中单击对象资源管理器中的安全文件夹，|unordered-list-item|3031568|右键单击Schemas+|3031569|选择"New+Schema..."|3031570|Named+my+new+schema+(在本例中为exe)|3031571|Hit+OK|3031572|<>F211|3031573|我发现这篇文章更改了模式，但在尝试更改到新模式时也出现了相同的权限错误。我的SSMS中列出了几个数据库，所以我只是尝试指定数据库，它起作用了：|3031574|USE+(yourservername)++
ALTER+SCHEMA+exe+TRANSFER+dbo.Employees+|code-block|syntax|javascript|3031575|entityMap^0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|10|8|@]|9|@]|A|$]]|$1|F|3|G|5|E|7|11|8|@]|9|@]|A|$]]|$1|H|3|I|5|E|7|12|8|@]|9|@]|A|$]]|$1|J|3|K|5|E|7|13|8|@]|9|@]|A|$]]|$1|L|3|M|5|E|7|14|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|15|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|16|8|@]|9|@]|A|$]]|$1|R|3|S|5|T|7|17|8|@]|9|@]|A|$U|V]]|$1|W|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|X|$]]

Through SSMS, I created a new schema by:

<ul>
<li>Clicking the Security folder in the Object Explorer within my server,</li>
<li>right clicked Schemas </li>
<li>Selected "New Schema..."</li>
<li>Named my new schema (exe in your case)</li>
<li>Hit OK</li>
</ul>

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: 

<pre><code>USE (yourservername) 
ALTER SCHEMA exe TRANSFER dbo.Employees 
</code></pre>

blocks|key|196427|text|你的代码是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|196428|FROM
+dbo.Employees
TO
+exe.Employees|code-block|syntax|javascript|196429|我试着使用这个查询。|196430|ALTER+SCHEMA+exe+TRANSFER+dbo.Employees|196431|只需编写create+schema+exe并执行它|offset|length|style|CODE|196432|entityMap^0|0|0|0|0|4|H|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|W|8|@$M|X|N|Y|O|P]]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

Your Code is: 

<pre><code>FROM
 dbo.Employees
TO
 exe.Employees
</code></pre>

I tried with this query.

<pre><code>ALTER SCHEMA exe TRANSFER dbo.Employees
</code></pre>

Just write <code>create schema exe</code> and execute it

blocks|key|3031476|text|查看MSDN...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3031477|CREATE+SCHEMA：http://msdn.microsoft.com/en-us/library/ms189462.aspx|offset|length|style|CODE|3031478|然后|3031479|ALTER+SCHEMA：http://msdn.microsoft.com/en-us/library/ms173423.aspx|3031480|或者你可以把它打开所以..。|3031481|How+do+I+move+a+table+into+a+schema+in+T-SQL|3031482|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms189462.aspx|1|http://msdn.microsoft.com/en-us/library/ms173423.aspx|2|https://stackoverflow.com/questions/1149159/how-do-i-move-a-table-into-a-schema-in-t-sql^0|0|0|D|E|1H|0|0|0|0|C|D|1H|1|0|0|0|18|2|0^^$0|@$1|2|3|4|5|6|7|11|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|12|8|@$D|13|E|14|F|G]]|9|@$D|15|E|16|1|17]]|A|$]]|$1|H|3|I|5|6|7|18|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|19|8|@$D|1A|E|1B|F|G]]|9|@$D|1C|E|1D|1|1E]]|A|$]]|$1|L|3|M|5|6|7|1F|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|1G|8|@]|9|@$D|1H|E|1I|1|1J]]|A|$]]|$1|P|3|-4|5|6|7|1K|8|@]|9|@]|A|$]]]|Q|$R|$5|S|T|U|A|$V|W]]|X|$5|S|T|U|A|$V|Y]]|Z|$5|S|T|U|A|$V|10]]]]

Check out MSDN...

<code>CREATE SCHEMA</code>: <a href="http://msdn.microsoft.com/en-us/library/ms189462.aspx" rel="nofollow noreferrer">http://msdn.microsoft.com/en-us/library/ms189462.aspx</a>

Then

<code>ALTER SCHEMA</code>: <a href="http://msdn.microsoft.com/en-us/library/ms173423.aspx" rel="nofollow noreferrer">http://msdn.microsoft.com/en-us/library/ms173423.aspx</a>

Or you can check it on on SO...

<a href="https://stackoverflow.com/questions/1149159/how-do-i-move-a-table-into-a-schema-in-t-sql">How do I move a table into a schema in T-SQL</a>

blocks|key|192245|text|以防有人想要更低的版本-|type|unstyled|depth|inlineStyleRanges|entityRanges|data|192246|对于SQL+Server+2000：|192247|sp_changeobjectowner+@objname+=+'dbo.Employess‘，@newowner+='exe’|192248|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

In case, someone looking for lower version -

For SQL Server 2000:

sp_changeobjectowner @objname = 'dbo.Employess' , @newowner ='exe'

blocks|key|2819087|text|确保您在SSMS中处于正确的数据库上下文中。得到了和你一样的错误，但是我知道模式已经存在了。我没有意识到我是在“大师”的背景下。在我将上下文更改为我的数据库后，ALTER起作用了。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2819088|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|193897|text|在sql中重命名对象时要非常非常小心。如果你没有完全离开你正在做的事情，你可能会导致依赖失败。已经说过，如果你对环境有适当的访问权限，这很容易(太多了)重命名对象：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|193898|exec+sp_rename+'Nameofobject',+'ReNameofobject'|code-block|syntax|javascript|193899|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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:

<pre><code>exec sp_rename 'Nameofobject', 'ReNameofobject'
</code></pre>

blocks|key|194074|text|您还可以将数据从默认架构'dbo‘从向导传输到您的架构，方法是:1-双击数据库图2-右键单击您的证书实体-->选择属性3-在标识右侧，更改架构名称|type|unstyled|depth|inlineStyleRanges|entityRanges|data|194075|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

also you can transfer your data from default schema 'dbo' to your schema
from wizard by
1-double click on db diagram
2- right click on your certian entity --&gt; select properties
3- on right at identity , change the schema name

I want to change schema name of table <code>Employees</code> in Database. In the current table <code>Employees</code> database schema name is <code>dbo</code> I want to change it to <code>exe</code>. How can I do it ? 

Example:

FROM 

<pre><code>dbo.Employees
</code></pre>

TO

<pre><code>exe.Employees
</code></pre>

I tried with this query:

<pre><code>ALTER SCHEMA exe TRANSFER dbo.Employees
</code></pre>

But this gives me an error:

<blockquote>
 Cannot alter the schema 'exe', because it does not exist or you do not
 have permission.
</blockquote>

What did I miss?

Change Schema Name Of Table In SQL

SQL Server

数据库

服务器

我想要更改数据库中表Employees的模式名称。在当前表中，Employees数据库模式名为dbo，我想将其更改为exe。我该怎么做呢？示例：从…dbo.Employees至exe.Employees我尝试使用这个查询：ALTER SCHEMA exe TRANSFER dbo.Employees但这给了我一个错误：无法更改架构'exe'，因为它不存在或您没有权限。我错过了什么？

问在SQL中更改表的架构名称
EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在SQL中更改表的架构名称EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在SQL中更改表的架构名称
EN