How can I delete a duplicated table

Question

Post reply

How can I delete a duplicated table

Doug Simmons

Ten Centuries

Points: 1373
More actions
May 2, 2014 at 11:24 pm

#288381

I created a new table but accidentally called a foreign key AgentsMasterID when it should have been AgentMasterID.
I went to Management Studio and opened the table in Design mode and changed the column name. When I clicked Save it appeared to fail. So I then used SQL to drop the table and recreate it with the correct foreign key name.
In Management Studio the new table all looked correct.
When I tried to insert data into the table, I got the error 'Invalid column name AgentMasterID'
When I look in sys.columns and sys.tables I can see that two tables exist with the same table name, one with the correct foreign key name and one with the incorrect foreign key name, but otherwise identical.
When I use DROP or ALTER TABLE, the newer correct table is affected. But if I try to INSERT data it looks as though the older incorrect table is accessed.
If I try to DROP the same table name twice, it errors - table does not exist or you do not have permission. In other words I can't find any way to access the incorrect table structure.
How can I remove the incorrect table?
Any help would be appreciated.
Doug

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

Execute this statement (with the correct schema and table name)

EXEC sp_help 'schema.table'

Then look for the constraint and Foreign key reference sections. Resolve those!

😎

Doug Simmons Ten Centuries Points: 1373 More actions · Answer 2

Running sp_help shows only the data for the correct new table and all the data is correct. There is no data related to the incorrect table.

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

Next step, generate the create script for the table. Make certain to go first to tools->options and under SQL Object Explorer->Scripting select all the relative options.

Let us know what you find:cool:

Doug Simmons Ten Centuries Points: 1373 More actions · Answer 4

The data returned shows the linked tables and the MasterAgentPayment create scripts and all the foreign key and default data is all corect, but it shows only the correct table data. There is no data from the old incorrect table.

I have attached a pic of the only way I can find to get the old table data to show up.

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 5

What does this return?

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'MasterAgentPayments'

Do you have the duplicate table in a different schema?

😎

Doug Simmons Ten Centuries Points: 1373 More actions · Answer 6

Oh you're right! The old table has somehow got added to my user name rather than dbo. So I have deleted it and now everything works correctly. Brilliant, I would never have dreamed that was possible.

Thanks so much.

Doug