Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
Author
Message
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 @ 7:11 AM
Points: 1,044, Visits: 2,930
Hi,

I want change schema name on Table.

hwdata.DesktopSoft_Master

to change

dbo.DesktopSoft_Master

I tried as below

ALTER SCHEMA HWData TRANSFER dbo.DesktopSoft_Master;

Error-
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.
Thanks
Post #1161672
Posted Thursday, August 18, 2011 7:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:40 PM
Points: 12,880, Visits: 31,802
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.



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1161744
Posted Thursday, August 18, 2011 7:23 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:47 AM
Points: 3,855, Visits: 4,994
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