In Microsoft SQL Server 2005, you can run the ALTER SCHEMA statement to move an object from one schema to another schema. However, when you run the ALTER SCHEMA statement, you may receive the following "Msg 15530" error message:
Msg 15530, Level 16, State 1, Line 1 The object with name "ObjectName" already exists.
This error message may be confusing if the destination schema does not appear to contain an object that has the same name as the object that you are trying to move.
This issue occurs if the following conditions are true:
You have two schemas. For example, you have Schema A and Schema B.
Schema A contains Table A. Table A is defined to use a primary key. The primary key is named ABC.
Schema B contains Table B. Table B is defined to use a primary key. This primary key is also named ABC.
In this scenario, if you try to run the ALTER SCHEMA statement to move Table A to Schema B, error 15530 occurs. You will also experience this issue if you try to move Table B to Schema A.
Internally, SQL Server stores information about the primary key for each table as an object in each schema. When you move a table to another schema, the primary key object for the table is also moved to the other schema. In the scenario that is mentioned earlier, the ALTER SCHEMA statement fails because the destination schema already contains a primary key ABC object.
This error message is not clear because the error message states that the table is the duplicate object. However, the primary key object that it associated with the table is actually the duplicate object.
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.
This behavior is by design.
When you are experiencing this issue, you receive an "Error 2601" error message if you use SQL Profiler to trace the operation. Error 2601 is a duplicate key error. The error message resembles the following:
Error: 2601, Severity: 14, State: 1
This error occurs on object 34, index id 2. The object is the sys.sysschobjs system table. The index is nc1. Index nc1 is defined to be unique on the following columns:
Therefore, error 2601 occurs because you try to insert a duplicate row in the sys.sysschobjs system table for index nc1.
For example, in the scenario that is mentioned earlier, assume that the value in the nsid column for Schema A is 5. Additionally, assume that the value in the nsid column for Schema B is 1. You run the following statement to query the sys.sysschobjs system table:
select nsclass, nsid, name from sys.sysschobjs where name = 'ABC'
This information shows that, when you run the ALTER SCHEMA statement to try to change the nsid from 5 (Schema A) to 1 (Schema B), you are trying to add a duplicate row in the sys.sysschobjs system table for index nc1. Therefore, error 15530 occurs, and you receive the error message that is mentioned in the “Symptoms” section. The underlying behavior of error 15530 is that error 2601 occurs on the sys.sysschobjs system table when you run the ALTER SCHEMA statement.
Steps to reproduce the behavior
Open SQL Server Management Studio, and then connect to an instance of SQL Server 2005.
Create a new query, and then run the following statements:
drop table [dbo].[etable]drop table schemaa.[ftable]gocreate table [dbo].[etable] (a int not null)create table schemaa.[ftable] (a int not null)goalter table [dbo].etable add constraint abc primary key (a) alter table schemaa.ftable add constraint abc primary key (a)goALTER SCHEMA [dbo] TRANSFER schemaa.[ftable]
Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Standard X64 Edition, Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems, Microsoft SQL Server 2005 Enterprise X64 Edition, Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems, Microsoft SQL Server 2005 Workgroup Edition, Microsoft SQL Server 2005 Express Edition, Microsoft SQL Server 2005 Express Edition with Advanced Services