locked
DROP Table - How to force a delete of a table when constraints are missing? RRS feed

  • Question

  • Some tables were created in the wrong DB.   I am trying to remove them, but some tables cannot be dropped because they reference constraints that are missing.

    Thanks for any ideas,

    Tom


    MisterT99
    Thursday, January 19, 2012 4:51 PM

Answers

  • yes,  I have found a resolution.   I ran the following to get a list of constraints.  Then I deleted each one which allowed me to remove the unused tables.

     

    SELECT f.name AS ForeignKey,
    OBJECT_NAME(f.parent_object_id) AS TableName,
    COL_NAME(fc.parent_object_id,
    fc.parent_column_id) AS ColumnName,
    OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
    COL_NAME(fc.referenced_object_id,
    fc.referenced_column_id) AS ReferenceColumnName
    FROM sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc
    ON f.OBJECT_ID = fc.constraint_object_id
    

    Thanks

     


    MisterT99
    • Marked as answer by Mister T99 Tuesday, January 24, 2012 2:27 PM
    Tuesday, January 24, 2012 2:27 PM

All replies

  • Not very clear..If you are not able to delete any tables because of existing constraints like foreign key , then first delete the constraint and then the tables

    Thanks and regards, Rishabh , Microsoft Community Contributor
    • Proposed as answer by Manish_Kaushik Thursday, January 19, 2012 5:00 PM
    • Unproposed as answer by Kalman Toth Tuesday, January 24, 2012 6:34 AM
    Thursday, January 19, 2012 4:57 PM
  • Drop the constraint first (or disable it, option NOCHECK)

    Then drop the table

    http://msdn.microsoft.com/en-us/library/aa275462(v=SQL.80).aspx

    Thursday, January 19, 2012 4:57 PM
  • Try deleting them manually from the Object Explorer.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, January 19, 2012 5:06 PM
  • Sygrien,

    Thank you for your reply.   I tried that and got an error (SQL 2005).  Here is the example:

     

    ALTER TABLE [dbo].[Desktop] DROP CONSTRAINT [DF_Desktop_Features] WITH NOCHECK

    Incorrect syntax near the keyword 'NOCHECK'.


    MisterT99
    Thursday, January 19, 2012 5:09 PM
  • If you DROP  the constraint, you don't need "WITH NOCHECK"

    Also, try Naomi's suggestion (remove them by hand).

    Thursday, January 19, 2012 5:15 PM
  • when I delete in Object Explorer I get this error:

     

    The Default 'DF_Desktop_ContentItemId' does not exist on the server.

     


    MisterT99
    Thursday, January 19, 2012 5:25 PM
  • Can you script this table you're trying to delete? (Script create). 
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, January 19, 2012 5:27 PM
  • ALTER TABLE [dbo].[Desktop] DROP CONSTRAINT [DF_Desktop_SupportedFeatures]
    
    ALTER TABLE [dbo].[Desktop] DROP CONSTRAINT [DF_Desktop_PackageID] 
    
    ALTER TABLE [dbo].[Desktop] DROP CONSTRAINT [DF_Desktop_ContentItemId] 
    
    /****** Object:  Table [dbo].[Desktop]    Script Date: 01/19/2012 10:46:18 ******/
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Desktop]') AND type in (N'U'))
    DROP TABLE [dbo].[Desktop]
    
    


    MisterT99
    Thursday, January 19, 2012 5:31 PM
  • Any progress?

    Can you post the exact error messages? Thanks.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES
    Tuesday, January 24, 2012 6:35 AM
  • yes,  I have found a resolution.   I ran the following to get a list of constraints.  Then I deleted each one which allowed me to remove the unused tables.

     

    SELECT f.name AS ForeignKey,
    OBJECT_NAME(f.parent_object_id) AS TableName,
    COL_NAME(fc.parent_object_id,
    fc.parent_column_id) AS ColumnName,
    OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
    COL_NAME(fc.referenced_object_id,
    fc.referenced_column_id) AS ReferenceColumnName
    FROM sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc
    ON f.OBJECT_ID = fc.constraint_object_id
    

    Thanks

     


    MisterT99
    • Marked as answer by Mister T99 Tuesday, January 24, 2012 2:27 PM
    Tuesday, January 24, 2012 2:27 PM