Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 trying to delete a table

Author  Topic 

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-02-18 : 11:51:48
Hi

I was trying to alter a table and make the varchar sizes bigger on the relevant columns. I kept getting this error saying I could not delete because of a dependency (pk) but the table is stand alone, i just created it.

So tried to just drop the table and start again with bigger varchar values in the create table statement but when I run create table it's saying:

There is already an object named 'MyTable' in the database.

But before this I ran the drop table and it said successful?

Then if I try to drop it again it fails saying no object called 'MyTable' found.

Refreshed the DB and it disappeard from object list but it still says the object exists.

What is wrong, one part says it exists the other says it doesn't exist?

Thanks

G


visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-18 : 12:00:02
You need to first drop the pk constraint and then drop the table.

And you need to make sure you delete the reference data from other tables refering this table before you drop the master table.


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

srimami
Posting Yak Master

160 Posts

Posted - 2013-02-18 : 18:00:10
Go to tasks in object explorer, go to scripts for Drop and Create and change the table columns as required. Also, make sure you delete the reference data from other tables referring this table like Visakh said.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-02-18 : 18:25:04
You don't need to drop a PK in order to drop a table. Are you running these commands from Mangement Studio? If so, can you verify that you are connected to the right database and not some other DB (like master)?
Go to Top of Page

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-02-19 : 09:53:08
I reopened the DB, opened the file to create the table and it asked me to connect again (no idea why) then I tried running it and it said object already exists. So I checked the list of DB and was in Master. Changed it to my own DB and now it worked.

the table was stand alone it had no dependencies so was no linked data in any other table.
Go to Top of Page

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-02-19 : 10:07:03
I have redgate SQL Prompt added to give me Intellisense in SQL Server 2005. When I type for instance:

ALTER TABLE I get lists up but do not get this table I created. I get system tables and everything else plus DB names. If I select my DB name then type:

MyDB.dbo.

I would expect to get this table name to show in the intellisense but it doesn't. I am in my own DB and I have one other table created and that shows in the list. Any ideas why and what this might be a symptom of?

Thanks

G
Go to Top of Page
   

- Advertisement -