6

I have database that has multiple schemas and objects under them. I want to consolidate the objects into one schema.

I tried

ALTER SCHEMA dbo TRANSFER <custom_schema>.<table_name>

I get an

object already exists message

However, I can't see it in the Management studio and 

SELECT * from dbo.<table_name>

returns

object does not exist.

Looks like some system table entry is out of whack. I looked at sysobjects and it has only one entry for . Any suggestions on how to trouble shoot/ fix this issue is welcome.

Note: I can create a synonym

CREATE SYNONYM dbo.<table_name> FOR <custom_schema>.<table_name>

works fine

Dale K
  • 25,246
  • 15
  • 42
  • 71
shikarishambu
  • 2,219
  • 6
  • 35
  • 57

1 Answers1

14

According to this MSDN page your issue may be caused by a duplicate primary key name. i.e. your table_name's primary key name conflicts with a primary key name already defined within some other table in dbo.

To resolve this issue, rename the primary key for the table that you want to move. Use a name that does not appear as a primary key in the destination schema.

Jeff Swensen
  • 3,513
  • 28
  • 52
  • I ran into this problem today, changed the PK name, altered the schema, then changed the PK name back to its original name and it worked perfectly! – Jamie Aug 22 '12 at 19:14
  • This could apply to names of any constraints on the table being moved, including default, foreign key, and check constraints. Since names of such constraints [must be unique within a schema](https://stackoverflow.com/a/19832399/303101), moving a table to a different schema will cause the above mentioned error if that would result in a constraint name clash. The error message produced by SQL Server is very unhelpful. – Reversed Engineer Dec 05 '22 at 13:21