SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 trying to delete a table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Grifter
Constraint Violating Yak Guru

260 Posts

Posted - 02/18/2013 :  11:51:48  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 02/18/2013 :  12:00:02  Show Profile  Reply with Quote
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 - 02/18/2013 :  18:00:10  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 02/18/2013 :  18:25:04  Show Profile  Reply with Quote
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

260 Posts

Posted - 02/19/2013 :  09:53:08  Show Profile  Reply with Quote
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.

Edited by - Grifter on 02/19/2013 09:55:04
Go to Top of Page

Grifter
Constraint Violating Yak Guru

260 Posts

Posted - 02/19/2013 :  10:07:03  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000