Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to drop a table, and this table has be many table's foreign key.

So I want to drop all those reference (just refernece, not other table) within the drop table process.

Is there any simple SQL, such as:

Drop table table_name with all reference ?

share|improve this question

migrated from stackoverflow.com Mar 22 at 0:18

This question came from our site for professional and enthusiast programmers.

    
your tags are confusing me. do you want a solution for mysql or for oracle? –  Leo Mar 21 at 23:56
    
mysql, I think maybe there are some STANDARD sql, if not, then mysql –  lovespring Mar 22 at 0:08
    
there isn't a standard SQL for that. SQL spec is made by ANSI. –  Leo Mar 22 at 0:10
    
notice that despite many DBMSes claim they implement ANSI-92 or ANSI-xyz, in the small letters, you will find that all of them have exceptions and corner cases. –  Leo Mar 22 at 0:12
    
DROP TABLE TableName; Will Itself drop all FKeys. –  Abdul Manaf Mar 22 at 7:37

2 Answers 2

up vote 2 down vote accepted

You can use the following:

The following query will generate all the keys which you have to drop auto, then simply run:

SELECT CONCAT ('alter table ',
               TABLE_SCHEMA,
               '.',
               TABLE_NAME,
               ' DROP FOREIGN KEY ',
               CONSTRAINT_NAME,
               ';')
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
 WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
   AND TABLE_NAME = 'YourTableName'; 
share|improve this answer
    
This will drop all the foreign keys, right? But I suppose the OP wants to drop only the ones related to one specific table. –  ypercube Mar 24 at 9:08
    
You can add the condition: and TABLE_NAME = 'YourTableName'; –  Adel Mar 24 at 9:14
    
You can edit your answer. –  ypercube Mar 24 at 9:14

Directly drop table is not Possible in Sql Server Because Normally All the table References from one or more Table .

So First you have to Disable all the FOREIGN KEY CONSTRAINTS in your Database

So First you have to run this Query:

WHILE(EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY' --OR CONSTRAINT_TYPE='PRIMARY KEY' )) BEGIN DECLARE @SQL NVARCHAR(2000) SELECT TOP 1 @SQL=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']') FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' -- OR CONSTRAINT_TYPE='PRIMARY KEY' EXEC (@SQL) END

-- SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS(With you r References)

Then you Run this Query;

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

share|improve this answer
    
I don't think EXEC sp_MSforeachtable will run in MySQL. –  ypercube Mar 24 at 9:07
    
This Query is Applicable for SQLServer 2008 R2 –  user36062 Mar 24 at 9:16
    
I know, but the question has a [mysql] tag. –  ypercube Mar 24 at 9:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.