Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

how to change new schema name from existing schema - Table Expand / Collapse
Posted Thursday, August 18, 2011 5:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:49 PM
Points: 1,179, Visits: 3,453

I want change schema name on Table.


to change


I tried as below

ALTER SCHEMA HWData TRANSFER dbo.DesktopSoft_Master;

Msg 15530, Level 16, State 1, Line 1
The object with name "DesktopSoft_Master" already exists.

Can you give me script for change new schema Object.
Post #1161672
Posted Thursday, August 18, 2011 7:19 AM



Group: General Forum Members
Last Login: Today @ 3:19 PM
Points: 14,485, Visits: 38,193
that error says the table already exists in the hwdata schema.
do a quick
select * from hwdata.DesktopSoft_Master
select * from dbo.DesktopSoft_Master

decide which one to keep (say dbo was the "good one"
then drop table hwdata.DesktopSoft_Master
and try to transfer again.


help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1161744
Posted Thursday, August 18, 2011 7:23 AM



Group: General Forum Members
Last Login: Yesterday @ 11:32 PM
Points: 5,300, Visits: 7,010
You appear to have transposed the schemas

The correct syntax is

ALTER SCHEMA the destination schema TRASFER Object::current schema.TableName;

so, using your case:
ALTER SCHEMA dbo TRANSFER HWData.DesktopSoft_Master;

Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1161748
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse