SQL DROP TABLE foreign key constraint












123















If I want to delete all the tables in my database like this, will it take care of the foreign key constraint? If not, how do I take care of that first?



GO
IF OBJECT_ID('dbo.[Course]','U') IS NOT NULL
DROP TABLE dbo.[Course]
GO
IF OBJECT_ID('dbo.[Student]','U') IS NOT NULL
DROP TABLE dbo.[Student]









share|improve this question

























  • Check this link

    – Yordan Georgiev
    Mar 22 '10 at 6:49






  • 1





    Check this answer, it helped me ;) stackoverflow.com/a/5488670/2205144

    – xatz
    Aug 7 '15 at 16:32
















123















If I want to delete all the tables in my database like this, will it take care of the foreign key constraint? If not, how do I take care of that first?



GO
IF OBJECT_ID('dbo.[Course]','U') IS NOT NULL
DROP TABLE dbo.[Course]
GO
IF OBJECT_ID('dbo.[Student]','U') IS NOT NULL
DROP TABLE dbo.[Student]









share|improve this question

























  • Check this link

    – Yordan Georgiev
    Mar 22 '10 at 6:49






  • 1





    Check this answer, it helped me ;) stackoverflow.com/a/5488670/2205144

    – xatz
    Aug 7 '15 at 16:32














123












123








123


42






If I want to delete all the tables in my database like this, will it take care of the foreign key constraint? If not, how do I take care of that first?



GO
IF OBJECT_ID('dbo.[Course]','U') IS NOT NULL
DROP TABLE dbo.[Course]
GO
IF OBJECT_ID('dbo.[Student]','U') IS NOT NULL
DROP TABLE dbo.[Student]









share|improve this question
















If I want to delete all the tables in my database like this, will it take care of the foreign key constraint? If not, how do I take care of that first?



GO
IF OBJECT_ID('dbo.[Course]','U') IS NOT NULL
DROP TABLE dbo.[Course]
GO
IF OBJECT_ID('dbo.[Student]','U') IS NOT NULL
DROP TABLE dbo.[Student]






sql-server foreign-keys constraints database-table drop-table






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Aug 17 '14 at 14:19









Sam

5,40883559




5,40883559










asked Nov 21 '09 at 17:15







user188229




















  • Check this link

    – Yordan Georgiev
    Mar 22 '10 at 6:49






  • 1





    Check this answer, it helped me ;) stackoverflow.com/a/5488670/2205144

    – xatz
    Aug 7 '15 at 16:32



















  • Check this link

    – Yordan Georgiev
    Mar 22 '10 at 6:49






  • 1





    Check this answer, it helped me ;) stackoverflow.com/a/5488670/2205144

    – xatz
    Aug 7 '15 at 16:32

















Check this link

– Yordan Georgiev
Mar 22 '10 at 6:49





Check this link

– Yordan Georgiev
Mar 22 '10 at 6:49




1




1





Check this answer, it helped me ;) stackoverflow.com/a/5488670/2205144

– xatz
Aug 7 '15 at 16:32





Check this answer, it helped me ;) stackoverflow.com/a/5488670/2205144

– xatz
Aug 7 '15 at 16:32












13 Answers
13






active

oldest

votes


















281














No, this will not drop your table if there are indeed foreign keys referencing it.



To get all foreign key relationships referencing your table, you could use this SQL (if you're on SQL Server 2005 and up):



SELECT * 
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('Student')


and if there are any, with this statement here, you could create SQL statements to actually drop those FK relations:



SELECT 
'ALTER TABLE [' + OBJECT_SCHEMA_NAME(parent_object_id) +
'].[' + OBJECT_NAME(parent_object_id) +
'] DROP CONSTRAINT [' + name + ']'
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('Student')





share|improve this answer


























  • Note: the generated table contains the statements to delete the individual constraints to be run in a different query. It worked wonders in my case since I needed to get rid of an useless table (for testing purposes) while maintaining the rest of the tables that had FK to it intact.

    – Mauricio Quintana
    Jul 24 '13 at 18:07








  • 1





    I had to use parent_object_id instead of referenced_object_id

    – Tom Robinson
    Feb 3 '15 at 14:03






  • 2





    An alternative to the SELECT statement for getting all referencing tables is: EXEC sp_fkeys 'Student';

    – Buggieboy
    May 15 '15 at 21:03











  • small adjustment this query SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [name] ' FROM sys.foreign_keys WHERE referenced_object_id = object_id('Student') replace [name] with CONSTRAINT name

    – Stas Svishov
    Jun 9 '15 at 12:55













  • works good thanks. (I got an error because I needed to put foreign key name into [ ] . )

    – Vlad
    May 14 '16 at 10:59



















30














In SQL Server Management Studio 2008 (R2) and newer, you can Right Click on the




DB -> Tasks -> Generate Scripts





  • Select the tables you want to DROP.


  • Select "Save to new query window".


  • Click on the Advanced button.


  • Set Script DROP and CREATE to Script DROP.


  • Set Script Foreign Keys to True.


  • Click OK.


  • Click Next -> Next -> Finish.


  • View the script and then Execute.







share|improve this answer





















  • 10





    still the same 'Could not drop object 'my_table' because it is referenced by a FOREIGN KEY constraint.

    – FrenkyB
    Nov 16 '15 at 13:00






  • 4





    How this answer has 28 + rating is beyond me... still doesn't work mate

    – AltF4_
    Jul 24 '17 at 9:32






  • 1





    While it does generate the script - that script does not solve the problem, which is drop the table referenced by Foreign Key constraints.

    – Alexey Shevelyov
    Feb 14 '18 at 15:12



















16














If you drop the "child" table first, the foreign key will be dropped as well. If you attempt to drop the "parent" table first, you will get an "Could not drop object 'a' because it is referenced by a FOREIGN KEY constraint." error.






share|improve this answer



















  • 1





    True, but no solution. Child can have foreign keys to oher tables and/or you might not want to drop it in the first place.

    – MaR
    Nov 21 '09 at 18:17






  • 4





    True, and a solution if the goal is, as stated, "If I want to delete all the tables in my database..."

    – Philip Kelley
    Nov 23 '09 at 2:25






  • 4





    this is a much better answer than the accepted one, given that the questioner wants to "delete all the tables in my database"

    – lukkea
    Jan 23 '12 at 8:43



















10














Here is another way to drop all tables correctly, using sp_MSdropconstraints procedure. The shortest code I could think of:



exec sp_MSforeachtable "declare @name nvarchar(max); set @name = parsename('?', 1); exec sp_MSdropconstraints @name";
exec sp_MSforeachtable "drop table ?";





share|improve this answer































    2














    If it is SQL Server you must drop the constraint before you can drop the table.






    share|improve this answer































      2














      Slightly more generic version of what @mark_s posted, this helped me



      SELECT 
      'ALTER TABLE ' + OBJECT_SCHEMA_NAME(k.parent_object_id) +
      '.[' + OBJECT_NAME(k.parent_object_id) +
      '] DROP CONSTRAINT ' + k.name
      FROM sys.foreign_keys k
      WHERE referenced_object_id = object_id('your table')


      just plug your table name, and execute the result of it.






      share|improve this answer
























      • Hi, I've been trying to drop my foreign key constraints by the above, but when I go to drop a table afterwards, it keeps saying "Could not drop the object because it still is being referenced by a Foreign Key constraint...any ideas? Thanks in advance.

        – daniness
        Dec 12 '17 at 19:11



















      1














      Here's another way to do delete all the constraints followed by the tables themselves, using a concatenation trick involving FOR XML PATH('') which allows merging multiple input rows into a single output row. Should work on anything SQL 2005 & later.



      I've left the EXECUTE commands commented out for safety.



      DECLARE @SQL NVARCHAR(max)
      ;WITH fkeys AS (
      SELECT quotename(s.name) + '.' + quotename(o.name) tablename, quotename(fk.name) constraintname
      FROM sys.foreign_keys fk
      JOIN sys.objects o ON fk.parent_object_id = o.object_id
      JOIN sys.schemas s ON o.schema_id = s.schema_id
      )
      SELECT @SQL = STUFF((SELECT '; ALTER TABLE ' + tablename + ' DROP CONSTRAINT ' + constraintname
      FROM fkeys
      FOR XML PATH('')),1,2,'')

      -- EXECUTE(@sql)

      SELECT @SQL = STUFF((SELECT '; DROP TABLE ' + quotename(TABLE_SCHEMA) + '.' + quotename(TABLE_NAME)
      FROM INFORMATION_SCHEMA.TABLES
      FOR XML PATH('')),1,2,'')

      -- EXECUTE(@sql)





      share|improve this answer
























      • This doesn't work well because of not all FK will be dropped (only those where our tables is used as parent, where we should drop also constraint where our table was used as "referenced" ).

        – Roman Pokrovskij
        Dec 27 '13 at 10:05











      • Roman, the original post is about deleting foreign key constraints. My answer doesn't aim to cover anything beyond that.

        – Warren Rumak
        Dec 27 '13 at 13:56





















      1














      Here is a complete script to implement a solution:



      create Procedure [dev].DeleteTablesFromSchema
      (
      @schemaName varchar(500)
      )
      As
      begin
      declare @constraintSchemaName nvarchar(128), @constraintTableName nvarchar(128), @constraintName nvarchar(128)
      declare @sql nvarchar(max)
      -- delete FK first
      declare cur1 cursor for
      select distinct
      CASE WHEN t2.[object_id] is NOT NULL THEN s2.name ELSE s.name END as SchemaName,
      CASE WHEN t2.[object_id] is NOT NULL THEN t2.name ELSE t.name END as TableName,
      CASE WHEN t2.[object_id] is NOT NULL THEN OBJECT_NAME(d2.constraint_object_id) ELSE OBJECT_NAME(d.constraint_object_id) END as ConstraintName
      from sys.objects t
      inner join sys.schemas s
      on t.[schema_id] = s.[schema_id]
      left join sys.foreign_key_columns d
      on d.parent_object_id = t.[object_id]
      left join sys.foreign_key_columns d2
      on d2.referenced_object_id = t.[object_id]
      inner join sys.objects t2
      on d2.parent_object_id = t2.[object_id]
      inner join sys.schemas s2
      on t2.[schema_id] = s2.[schema_id]
      WHERE t.[type]='U'
      AND t2.[type]='U'
      AND t.is_ms_shipped = 0
      AND t2.is_ms_shipped = 0
      AND s.Name=@schemaName
      open cur1
      fetch next from cur1 into @constraintSchemaName, @constraintTableName, @constraintName
      while @@fetch_status = 0
      BEGIN
      set @sql ='ALTER TABLE ' + @constraintSchemaName + '.' + @constraintTableName+' DROP CONSTRAINT '+@constraintName+';'
      exec(@sql)
      fetch next from cur1 into @constraintSchemaName, @constraintTableName, @constraintName
      END
      close cur1
      deallocate cur1

      DECLARE @tableName nvarchar(128)
      declare cur2 cursor for
      select s.Name, p.Name
      from sys.objects p
      INNER JOIN sys.schemas s ON p.[schema_id] = s.[schema_id]
      WHERE p.[type]='U' and is_ms_shipped = 0
      AND s.Name=@schemaName
      ORDER BY s.Name, p.Name
      open cur2

      fetch next from cur2 into @schemaName,@tableName
      while @@fetch_status = 0
      begin
      set @sql ='DROP TABLE ' + @schemaName + '.' + @tableName
      exec(@sql)
      fetch next from cur2 into @schemaName,@tableName
      end

      close cur2
      deallocate cur2

      end
      go





      share|improve this answer

































        0















        If I want to delete all the tables in
        my database




        Then it's a lot easier to drop the entire database:



        DROP DATABASE WorkerPensions





        share|improve this answer



















        • 63





          Giving you a -1 for this as it is not a valid answer to the question, for two important reasons: 1) It deletes a lot more than tables! Stored procedures, functions, UDTs, security, .NET assemblies, etc. all go away with a DROP DATABASE. 2) You may not be allowed to create databases, e.g. centrally-managed dev environment where databases are provisioned by IT and have additional requirements at create-time that you aren't aware of.

          – Warren Rumak
          Dec 12 '13 at 21:30





















        0














        Removing Referenced FOREIGN KEY Constraints
        Assuming there is a parent and child table Relationship in SQL Server:

        --First find the name of the Foreign Key Constraint:
        SELECT *
        FROM sys.foreign_keys
        WHERE referenced_object_id = object_id('States')

        --Then Find foreign keys referencing to dbo.Parent(States) table:
        SELECT name AS 'Foreign Key Constraint Name',
        OBJECT_SCHEMA_NAME(parent_object_id) + '.' + OBJECT_NAME(parent_object_id) AS 'Child Table'
        FROM sys.foreign_keys
        WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'dbo' AND
        OBJECT_NAME(referenced_object_id) = 'dbo.State'

        -- Drop the foreign key constraint by its name
        ALTER TABLE dbo.cities DROP CONSTRAINT FK__cities__state__6442E2C9;

        -- You can also use the following T-SQL script to automatically find
        --and drop all foreign key constraints referencing to the specified parent
        -- table:

        BEGIN

        DECLARE @stmt VARCHAR(300);

        -- Cursor to generate ALTER TABLE DROP CONSTRAINT statements
        DECLARE cur CURSOR FOR
        SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.' +
        OBJECT_NAME(parent_object_id) +
        ' DROP CONSTRAINT ' + name
        FROM sys.foreign_keys
        WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'dbo' AND
        OBJECT_NAME(referenced_object_id) = 'states';

        OPEN cur;
        FETCH cur INTO @stmt;

        -- Drop each found foreign key constraint
        WHILE @@FETCH_STATUS = 0
        BEGIN
        EXEC (@stmt);
        FETCH cur INTO @stmt;
        END

        CLOSE cur;
        DEALLOCATE cur;

        END
        GO

        --Now you can drop the parent table:

        DROP TABLE states;
        --# Command(s) completed successfully.





        share|improve this answer

































          0














          Using SQL Server Manager you can drop foreign key constraints from the UI. If you want to delete the table Diary but the User table has a foreign key DiaryId pointing to the Diary table, you can expand (using the plus symbol) the User table and then expand the Foreign Keys section. Right click on the foreign key that points to the diary table then select Delete. You can then expand the Columns section, right click and delete the column DiaryId too. Then you can just run:



          drop table Diary




          I know your actual question is about deleting all tables, so this may not be a useful for that case. However, if you just want to delete a few tables this is useful I believe (the title does not explicitly mention deleting all tables).






          share|improve this answer

































            -1














            If you are on a mysql server and if you don't mind loosing your tables, you can use a simple query to delete multiple tables at once:



            SET foreign_key_checks = 0;
            DROP TABLE IF EXISTS table_a,table_b,table_c,table_etc;
            SET foreign_key_checks = 1;


            In this way it doesn't matter in what order you use the table in you query.



            If anybody is going to say something about the fact that this is not a good solution if you have a database with many tables: I agree!






            share|improve this answer


























            • I get Incorrect syntax near '='. (102) (SQLExecDirectW)

              – Matt
              Aug 7 '16 at 22:48











            • @Matt Bit hard to guess at which '=' you get that error.

              – Els den Iep
              Aug 8 '16 at 14:17






            • 2





              foreign_key_checks will not work on MSSQL server. I think that's a MySql specific variable.

              – ooXei1sh
              Oct 11 '16 at 16:33



















            -3














            If you want to DROP a table which has been referenced by other table using the foreign key use



            DROP TABLE *table_name* CASCADE CONSTRAINTS;

            I think it should work for you.






            share|improve this answer





















            • 1





              there is no cascade constraints in sql server

              – believesInSanta
              Feb 15 '16 at 13:39











            Your Answer






            StackExchange.ifUsing("editor", function () {
            StackExchange.using("externalEditor", function () {
            StackExchange.using("snippets", function () {
            StackExchange.snippets.init();
            });
            });
            }, "code-snippets");

            StackExchange.ready(function() {
            var channelOptions = {
            tags: "".split(" "),
            id: "1"
            };
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function() {
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled) {
            StackExchange.using("snippets", function() {
            createEditor();
            });
            }
            else {
            createEditor();
            }
            });

            function createEditor() {
            StackExchange.prepareEditor({
            heartbeatType: 'answer',
            autoActivateHeartbeat: false,
            convertImagesToLinks: true,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: 10,
            bindNavPrevention: true,
            postfix: "",
            imageUploader: {
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            },
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            });


            }
            });














            draft saved

            draft discarded


















            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f1776079%2fsql-drop-table-foreign-key-constraint%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown
























            13 Answers
            13






            active

            oldest

            votes








            13 Answers
            13






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            281














            No, this will not drop your table if there are indeed foreign keys referencing it.



            To get all foreign key relationships referencing your table, you could use this SQL (if you're on SQL Server 2005 and up):



            SELECT * 
            FROM sys.foreign_keys
            WHERE referenced_object_id = object_id('Student')


            and if there are any, with this statement here, you could create SQL statements to actually drop those FK relations:



            SELECT 
            'ALTER TABLE [' + OBJECT_SCHEMA_NAME(parent_object_id) +
            '].[' + OBJECT_NAME(parent_object_id) +
            '] DROP CONSTRAINT [' + name + ']'
            FROM sys.foreign_keys
            WHERE referenced_object_id = object_id('Student')





            share|improve this answer


























            • Note: the generated table contains the statements to delete the individual constraints to be run in a different query. It worked wonders in my case since I needed to get rid of an useless table (for testing purposes) while maintaining the rest of the tables that had FK to it intact.

              – Mauricio Quintana
              Jul 24 '13 at 18:07








            • 1





              I had to use parent_object_id instead of referenced_object_id

              – Tom Robinson
              Feb 3 '15 at 14:03






            • 2





              An alternative to the SELECT statement for getting all referencing tables is: EXEC sp_fkeys 'Student';

              – Buggieboy
              May 15 '15 at 21:03











            • small adjustment this query SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [name] ' FROM sys.foreign_keys WHERE referenced_object_id = object_id('Student') replace [name] with CONSTRAINT name

              – Stas Svishov
              Jun 9 '15 at 12:55













            • works good thanks. (I got an error because I needed to put foreign key name into [ ] . )

              – Vlad
              May 14 '16 at 10:59
















            281














            No, this will not drop your table if there are indeed foreign keys referencing it.



            To get all foreign key relationships referencing your table, you could use this SQL (if you're on SQL Server 2005 and up):



            SELECT * 
            FROM sys.foreign_keys
            WHERE referenced_object_id = object_id('Student')


            and if there are any, with this statement here, you could create SQL statements to actually drop those FK relations:



            SELECT 
            'ALTER TABLE [' + OBJECT_SCHEMA_NAME(parent_object_id) +
            '].[' + OBJECT_NAME(parent_object_id) +
            '] DROP CONSTRAINT [' + name + ']'
            FROM sys.foreign_keys
            WHERE referenced_object_id = object_id('Student')





            share|improve this answer


























            • Note: the generated table contains the statements to delete the individual constraints to be run in a different query. It worked wonders in my case since I needed to get rid of an useless table (for testing purposes) while maintaining the rest of the tables that had FK to it intact.

              – Mauricio Quintana
              Jul 24 '13 at 18:07








            • 1





              I had to use parent_object_id instead of referenced_object_id

              – Tom Robinson
              Feb 3 '15 at 14:03






            • 2





              An alternative to the SELECT statement for getting all referencing tables is: EXEC sp_fkeys 'Student';

              – Buggieboy
              May 15 '15 at 21:03











            • small adjustment this query SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [name] ' FROM sys.foreign_keys WHERE referenced_object_id = object_id('Student') replace [name] with CONSTRAINT name

              – Stas Svishov
              Jun 9 '15 at 12:55













            • works good thanks. (I got an error because I needed to put foreign key name into [ ] . )

              – Vlad
              May 14 '16 at 10:59














            281












            281








            281







            No, this will not drop your table if there are indeed foreign keys referencing it.



            To get all foreign key relationships referencing your table, you could use this SQL (if you're on SQL Server 2005 and up):



            SELECT * 
            FROM sys.foreign_keys
            WHERE referenced_object_id = object_id('Student')


            and if there are any, with this statement here, you could create SQL statements to actually drop those FK relations:



            SELECT 
            'ALTER TABLE [' + OBJECT_SCHEMA_NAME(parent_object_id) +
            '].[' + OBJECT_NAME(parent_object_id) +
            '] DROP CONSTRAINT [' + name + ']'
            FROM sys.foreign_keys
            WHERE referenced_object_id = object_id('Student')





            share|improve this answer















            No, this will not drop your table if there are indeed foreign keys referencing it.



            To get all foreign key relationships referencing your table, you could use this SQL (if you're on SQL Server 2005 and up):



            SELECT * 
            FROM sys.foreign_keys
            WHERE referenced_object_id = object_id('Student')


            and if there are any, with this statement here, you could create SQL statements to actually drop those FK relations:



            SELECT 
            'ALTER TABLE [' + OBJECT_SCHEMA_NAME(parent_object_id) +
            '].[' + OBJECT_NAME(parent_object_id) +
            '] DROP CONSTRAINT [' + name + ']'
            FROM sys.foreign_keys
            WHERE referenced_object_id = object_id('Student')






            share|improve this answer














            share|improve this answer



            share|improve this answer








            edited Apr 27 '17 at 9:25









            Community

            11




            11










            answered Nov 21 '09 at 17:24









            marc_smarc_s

            575k12811091256




            575k12811091256













            • Note: the generated table contains the statements to delete the individual constraints to be run in a different query. It worked wonders in my case since I needed to get rid of an useless table (for testing purposes) while maintaining the rest of the tables that had FK to it intact.

              – Mauricio Quintana
              Jul 24 '13 at 18:07








            • 1





              I had to use parent_object_id instead of referenced_object_id

              – Tom Robinson
              Feb 3 '15 at 14:03






            • 2





              An alternative to the SELECT statement for getting all referencing tables is: EXEC sp_fkeys 'Student';

              – Buggieboy
              May 15 '15 at 21:03











            • small adjustment this query SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [name] ' FROM sys.foreign_keys WHERE referenced_object_id = object_id('Student') replace [name] with CONSTRAINT name

              – Stas Svishov
              Jun 9 '15 at 12:55













            • works good thanks. (I got an error because I needed to put foreign key name into [ ] . )

              – Vlad
              May 14 '16 at 10:59



















            • Note: the generated table contains the statements to delete the individual constraints to be run in a different query. It worked wonders in my case since I needed to get rid of an useless table (for testing purposes) while maintaining the rest of the tables that had FK to it intact.

              – Mauricio Quintana
              Jul 24 '13 at 18:07








            • 1





              I had to use parent_object_id instead of referenced_object_id

              – Tom Robinson
              Feb 3 '15 at 14:03






            • 2





              An alternative to the SELECT statement for getting all referencing tables is: EXEC sp_fkeys 'Student';

              – Buggieboy
              May 15 '15 at 21:03











            • small adjustment this query SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [name] ' FROM sys.foreign_keys WHERE referenced_object_id = object_id('Student') replace [name] with CONSTRAINT name

              – Stas Svishov
              Jun 9 '15 at 12:55













            • works good thanks. (I got an error because I needed to put foreign key name into [ ] . )

              – Vlad
              May 14 '16 at 10:59

















            Note: the generated table contains the statements to delete the individual constraints to be run in a different query. It worked wonders in my case since I needed to get rid of an useless table (for testing purposes) while maintaining the rest of the tables that had FK to it intact.

            – Mauricio Quintana
            Jul 24 '13 at 18:07







            Note: the generated table contains the statements to delete the individual constraints to be run in a different query. It worked wonders in my case since I needed to get rid of an useless table (for testing purposes) while maintaining the rest of the tables that had FK to it intact.

            – Mauricio Quintana
            Jul 24 '13 at 18:07






            1




            1





            I had to use parent_object_id instead of referenced_object_id

            – Tom Robinson
            Feb 3 '15 at 14:03





            I had to use parent_object_id instead of referenced_object_id

            – Tom Robinson
            Feb 3 '15 at 14:03




            2




            2





            An alternative to the SELECT statement for getting all referencing tables is: EXEC sp_fkeys 'Student';

            – Buggieboy
            May 15 '15 at 21:03





            An alternative to the SELECT statement for getting all referencing tables is: EXEC sp_fkeys 'Student';

            – Buggieboy
            May 15 '15 at 21:03













            small adjustment this query SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [name] ' FROM sys.foreign_keys WHERE referenced_object_id = object_id('Student') replace [name] with CONSTRAINT name

            – Stas Svishov
            Jun 9 '15 at 12:55







            small adjustment this query SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [name] ' FROM sys.foreign_keys WHERE referenced_object_id = object_id('Student') replace [name] with CONSTRAINT name

            – Stas Svishov
            Jun 9 '15 at 12:55















            works good thanks. (I got an error because I needed to put foreign key name into [ ] . )

            – Vlad
            May 14 '16 at 10:59





            works good thanks. (I got an error because I needed to put foreign key name into [ ] . )

            – Vlad
            May 14 '16 at 10:59













            30














            In SQL Server Management Studio 2008 (R2) and newer, you can Right Click on the




            DB -> Tasks -> Generate Scripts





            • Select the tables you want to DROP.


            • Select "Save to new query window".


            • Click on the Advanced button.


            • Set Script DROP and CREATE to Script DROP.


            • Set Script Foreign Keys to True.


            • Click OK.


            • Click Next -> Next -> Finish.


            • View the script and then Execute.







            share|improve this answer





















            • 10





              still the same 'Could not drop object 'my_table' because it is referenced by a FOREIGN KEY constraint.

              – FrenkyB
              Nov 16 '15 at 13:00






            • 4





              How this answer has 28 + rating is beyond me... still doesn't work mate

              – AltF4_
              Jul 24 '17 at 9:32






            • 1





              While it does generate the script - that script does not solve the problem, which is drop the table referenced by Foreign Key constraints.

              – Alexey Shevelyov
              Feb 14 '18 at 15:12
















            30














            In SQL Server Management Studio 2008 (R2) and newer, you can Right Click on the




            DB -> Tasks -> Generate Scripts





            • Select the tables you want to DROP.


            • Select "Save to new query window".


            • Click on the Advanced button.


            • Set Script DROP and CREATE to Script DROP.


            • Set Script Foreign Keys to True.


            • Click OK.


            • Click Next -> Next -> Finish.


            • View the script and then Execute.







            share|improve this answer





















            • 10





              still the same 'Could not drop object 'my_table' because it is referenced by a FOREIGN KEY constraint.

              – FrenkyB
              Nov 16 '15 at 13:00






            • 4





              How this answer has 28 + rating is beyond me... still doesn't work mate

              – AltF4_
              Jul 24 '17 at 9:32






            • 1





              While it does generate the script - that script does not solve the problem, which is drop the table referenced by Foreign Key constraints.

              – Alexey Shevelyov
              Feb 14 '18 at 15:12














            30












            30








            30







            In SQL Server Management Studio 2008 (R2) and newer, you can Right Click on the




            DB -> Tasks -> Generate Scripts





            • Select the tables you want to DROP.


            • Select "Save to new query window".


            • Click on the Advanced button.


            • Set Script DROP and CREATE to Script DROP.


            • Set Script Foreign Keys to True.


            • Click OK.


            • Click Next -> Next -> Finish.


            • View the script and then Execute.







            share|improve this answer















            In SQL Server Management Studio 2008 (R2) and newer, you can Right Click on the




            DB -> Tasks -> Generate Scripts





            • Select the tables you want to DROP.


            • Select "Save to new query window".


            • Click on the Advanced button.


            • Set Script DROP and CREATE to Script DROP.


            • Set Script Foreign Keys to True.


            • Click OK.


            • Click Next -> Next -> Finish.


            • View the script and then Execute.








            share|improve this answer














            share|improve this answer



            share|improve this answer








            edited Dec 16 '16 at 10:21









            Stephan Bauer

            7,19822852




            7,19822852










            answered Sep 11 '13 at 14:23









            RiaanRiaan

            39932




            39932








            • 10





              still the same 'Could not drop object 'my_table' because it is referenced by a FOREIGN KEY constraint.

              – FrenkyB
              Nov 16 '15 at 13:00






            • 4





              How this answer has 28 + rating is beyond me... still doesn't work mate

              – AltF4_
              Jul 24 '17 at 9:32






            • 1





              While it does generate the script - that script does not solve the problem, which is drop the table referenced by Foreign Key constraints.

              – Alexey Shevelyov
              Feb 14 '18 at 15:12














            • 10





              still the same 'Could not drop object 'my_table' because it is referenced by a FOREIGN KEY constraint.

              – FrenkyB
              Nov 16 '15 at 13:00






            • 4





              How this answer has 28 + rating is beyond me... still doesn't work mate

              – AltF4_
              Jul 24 '17 at 9:32






            • 1





              While it does generate the script - that script does not solve the problem, which is drop the table referenced by Foreign Key constraints.

              – Alexey Shevelyov
              Feb 14 '18 at 15:12








            10




            10





            still the same 'Could not drop object 'my_table' because it is referenced by a FOREIGN KEY constraint.

            – FrenkyB
            Nov 16 '15 at 13:00





            still the same 'Could not drop object 'my_table' because it is referenced by a FOREIGN KEY constraint.

            – FrenkyB
            Nov 16 '15 at 13:00




            4




            4





            How this answer has 28 + rating is beyond me... still doesn't work mate

            – AltF4_
            Jul 24 '17 at 9:32





            How this answer has 28 + rating is beyond me... still doesn't work mate

            – AltF4_
            Jul 24 '17 at 9:32




            1




            1





            While it does generate the script - that script does not solve the problem, which is drop the table referenced by Foreign Key constraints.

            – Alexey Shevelyov
            Feb 14 '18 at 15:12





            While it does generate the script - that script does not solve the problem, which is drop the table referenced by Foreign Key constraints.

            – Alexey Shevelyov
            Feb 14 '18 at 15:12











            16














            If you drop the "child" table first, the foreign key will be dropped as well. If you attempt to drop the "parent" table first, you will get an "Could not drop object 'a' because it is referenced by a FOREIGN KEY constraint." error.






            share|improve this answer



















            • 1





              True, but no solution. Child can have foreign keys to oher tables and/or you might not want to drop it in the first place.

              – MaR
              Nov 21 '09 at 18:17






            • 4





              True, and a solution if the goal is, as stated, "If I want to delete all the tables in my database..."

              – Philip Kelley
              Nov 23 '09 at 2:25






            • 4





              this is a much better answer than the accepted one, given that the questioner wants to "delete all the tables in my database"

              – lukkea
              Jan 23 '12 at 8:43
















            16














            If you drop the "child" table first, the foreign key will be dropped as well. If you attempt to drop the "parent" table first, you will get an "Could not drop object 'a' because it is referenced by a FOREIGN KEY constraint." error.






            share|improve this answer



















            • 1





              True, but no solution. Child can have foreign keys to oher tables and/or you might not want to drop it in the first place.

              – MaR
              Nov 21 '09 at 18:17






            • 4





              True, and a solution if the goal is, as stated, "If I want to delete all the tables in my database..."

              – Philip Kelley
              Nov 23 '09 at 2:25






            • 4





              this is a much better answer than the accepted one, given that the questioner wants to "delete all the tables in my database"

              – lukkea
              Jan 23 '12 at 8:43














            16












            16








            16







            If you drop the "child" table first, the foreign key will be dropped as well. If you attempt to drop the "parent" table first, you will get an "Could not drop object 'a' because it is referenced by a FOREIGN KEY constraint." error.






            share|improve this answer













            If you drop the "child" table first, the foreign key will be dropped as well. If you attempt to drop the "parent" table first, you will get an "Could not drop object 'a' because it is referenced by a FOREIGN KEY constraint." error.







            share|improve this answer












            share|improve this answer



            share|improve this answer










            answered Nov 21 '09 at 17:24









            Philip KelleyPhilip Kelley

            32.9k94578




            32.9k94578








            • 1





              True, but no solution. Child can have foreign keys to oher tables and/or you might not want to drop it in the first place.

              – MaR
              Nov 21 '09 at 18:17






            • 4





              True, and a solution if the goal is, as stated, "If I want to delete all the tables in my database..."

              – Philip Kelley
              Nov 23 '09 at 2:25






            • 4





              this is a much better answer than the accepted one, given that the questioner wants to "delete all the tables in my database"

              – lukkea
              Jan 23 '12 at 8:43














            • 1





              True, but no solution. Child can have foreign keys to oher tables and/or you might not want to drop it in the first place.

              – MaR
              Nov 21 '09 at 18:17






            • 4





              True, and a solution if the goal is, as stated, "If I want to delete all the tables in my database..."

              – Philip Kelley
              Nov 23 '09 at 2:25






            • 4





              this is a much better answer than the accepted one, given that the questioner wants to "delete all the tables in my database"

              – lukkea
              Jan 23 '12 at 8:43








            1




            1





            True, but no solution. Child can have foreign keys to oher tables and/or you might not want to drop it in the first place.

            – MaR
            Nov 21 '09 at 18:17





            True, but no solution. Child can have foreign keys to oher tables and/or you might not want to drop it in the first place.

            – MaR
            Nov 21 '09 at 18:17




            4




            4





            True, and a solution if the goal is, as stated, "If I want to delete all the tables in my database..."

            – Philip Kelley
            Nov 23 '09 at 2:25





            True, and a solution if the goal is, as stated, "If I want to delete all the tables in my database..."

            – Philip Kelley
            Nov 23 '09 at 2:25




            4




            4





            this is a much better answer than the accepted one, given that the questioner wants to "delete all the tables in my database"

            – lukkea
            Jan 23 '12 at 8:43





            this is a much better answer than the accepted one, given that the questioner wants to "delete all the tables in my database"

            – lukkea
            Jan 23 '12 at 8:43











            10














            Here is another way to drop all tables correctly, using sp_MSdropconstraints procedure. The shortest code I could think of:



            exec sp_MSforeachtable "declare @name nvarchar(max); set @name = parsename('?', 1); exec sp_MSdropconstraints @name";
            exec sp_MSforeachtable "drop table ?";





            share|improve this answer




























              10














              Here is another way to drop all tables correctly, using sp_MSdropconstraints procedure. The shortest code I could think of:



              exec sp_MSforeachtable "declare @name nvarchar(max); set @name = parsename('?', 1); exec sp_MSdropconstraints @name";
              exec sp_MSforeachtable "drop table ?";





              share|improve this answer


























                10












                10








                10







                Here is another way to drop all tables correctly, using sp_MSdropconstraints procedure. The shortest code I could think of:



                exec sp_MSforeachtable "declare @name nvarchar(max); set @name = parsename('?', 1); exec sp_MSdropconstraints @name";
                exec sp_MSforeachtable "drop table ?";





                share|improve this answer













                Here is another way to drop all tables correctly, using sp_MSdropconstraints procedure. The shortest code I could think of:



                exec sp_MSforeachtable "declare @name nvarchar(max); set @name = parsename('?', 1); exec sp_MSdropconstraints @name";
                exec sp_MSforeachtable "drop table ?";






                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Oct 13 '14 at 20:04









                KraitKrait

                10112




                10112























                    2














                    If it is SQL Server you must drop the constraint before you can drop the table.






                    share|improve this answer




























                      2














                      If it is SQL Server you must drop the constraint before you can drop the table.






                      share|improve this answer


























                        2












                        2








                        2







                        If it is SQL Server you must drop the constraint before you can drop the table.






                        share|improve this answer













                        If it is SQL Server you must drop the constraint before you can drop the table.







                        share|improve this answer












                        share|improve this answer



                        share|improve this answer










                        answered Nov 21 '09 at 17:23









                        Shiraz BhaijiShiraz Bhaiji

                        48k26122228




                        48k26122228























                            2














                            Slightly more generic version of what @mark_s posted, this helped me



                            SELECT 
                            'ALTER TABLE ' + OBJECT_SCHEMA_NAME(k.parent_object_id) +
                            '.[' + OBJECT_NAME(k.parent_object_id) +
                            '] DROP CONSTRAINT ' + k.name
                            FROM sys.foreign_keys k
                            WHERE referenced_object_id = object_id('your table')


                            just plug your table name, and execute the result of it.






                            share|improve this answer
























                            • Hi, I've been trying to drop my foreign key constraints by the above, but when I go to drop a table afterwards, it keeps saying "Could not drop the object because it still is being referenced by a Foreign Key constraint...any ideas? Thanks in advance.

                              – daniness
                              Dec 12 '17 at 19:11
















                            2














                            Slightly more generic version of what @mark_s posted, this helped me



                            SELECT 
                            'ALTER TABLE ' + OBJECT_SCHEMA_NAME(k.parent_object_id) +
                            '.[' + OBJECT_NAME(k.parent_object_id) +
                            '] DROP CONSTRAINT ' + k.name
                            FROM sys.foreign_keys k
                            WHERE referenced_object_id = object_id('your table')


                            just plug your table name, and execute the result of it.






                            share|improve this answer
























                            • Hi, I've been trying to drop my foreign key constraints by the above, but when I go to drop a table afterwards, it keeps saying "Could not drop the object because it still is being referenced by a Foreign Key constraint...any ideas? Thanks in advance.

                              – daniness
                              Dec 12 '17 at 19:11














                            2












                            2








                            2







                            Slightly more generic version of what @mark_s posted, this helped me



                            SELECT 
                            'ALTER TABLE ' + OBJECT_SCHEMA_NAME(k.parent_object_id) +
                            '.[' + OBJECT_NAME(k.parent_object_id) +
                            '] DROP CONSTRAINT ' + k.name
                            FROM sys.foreign_keys k
                            WHERE referenced_object_id = object_id('your table')


                            just plug your table name, and execute the result of it.






                            share|improve this answer













                            Slightly more generic version of what @mark_s posted, this helped me



                            SELECT 
                            'ALTER TABLE ' + OBJECT_SCHEMA_NAME(k.parent_object_id) +
                            '.[' + OBJECT_NAME(k.parent_object_id) +
                            '] DROP CONSTRAINT ' + k.name
                            FROM sys.foreign_keys k
                            WHERE referenced_object_id = object_id('your table')


                            just plug your table name, and execute the result of it.







                            share|improve this answer












                            share|improve this answer



                            share|improve this answer










                            answered Jun 9 '15 at 13:03









                            Stas SvishovStas Svishov

                            2932416




                            2932416













                            • Hi, I've been trying to drop my foreign key constraints by the above, but when I go to drop a table afterwards, it keeps saying "Could not drop the object because it still is being referenced by a Foreign Key constraint...any ideas? Thanks in advance.

                              – daniness
                              Dec 12 '17 at 19:11



















                            • Hi, I've been trying to drop my foreign key constraints by the above, but when I go to drop a table afterwards, it keeps saying "Could not drop the object because it still is being referenced by a Foreign Key constraint...any ideas? Thanks in advance.

                              – daniness
                              Dec 12 '17 at 19:11

















                            Hi, I've been trying to drop my foreign key constraints by the above, but when I go to drop a table afterwards, it keeps saying "Could not drop the object because it still is being referenced by a Foreign Key constraint...any ideas? Thanks in advance.

                            – daniness
                            Dec 12 '17 at 19:11





                            Hi, I've been trying to drop my foreign key constraints by the above, but when I go to drop a table afterwards, it keeps saying "Could not drop the object because it still is being referenced by a Foreign Key constraint...any ideas? Thanks in advance.

                            – daniness
                            Dec 12 '17 at 19:11











                            1














                            Here's another way to do delete all the constraints followed by the tables themselves, using a concatenation trick involving FOR XML PATH('') which allows merging multiple input rows into a single output row. Should work on anything SQL 2005 & later.



                            I've left the EXECUTE commands commented out for safety.



                            DECLARE @SQL NVARCHAR(max)
                            ;WITH fkeys AS (
                            SELECT quotename(s.name) + '.' + quotename(o.name) tablename, quotename(fk.name) constraintname
                            FROM sys.foreign_keys fk
                            JOIN sys.objects o ON fk.parent_object_id = o.object_id
                            JOIN sys.schemas s ON o.schema_id = s.schema_id
                            )
                            SELECT @SQL = STUFF((SELECT '; ALTER TABLE ' + tablename + ' DROP CONSTRAINT ' + constraintname
                            FROM fkeys
                            FOR XML PATH('')),1,2,'')

                            -- EXECUTE(@sql)

                            SELECT @SQL = STUFF((SELECT '; DROP TABLE ' + quotename(TABLE_SCHEMA) + '.' + quotename(TABLE_NAME)
                            FROM INFORMATION_SCHEMA.TABLES
                            FOR XML PATH('')),1,2,'')

                            -- EXECUTE(@sql)





                            share|improve this answer
























                            • This doesn't work well because of not all FK will be dropped (only those where our tables is used as parent, where we should drop also constraint where our table was used as "referenced" ).

                              – Roman Pokrovskij
                              Dec 27 '13 at 10:05











                            • Roman, the original post is about deleting foreign key constraints. My answer doesn't aim to cover anything beyond that.

                              – Warren Rumak
                              Dec 27 '13 at 13:56


















                            1














                            Here's another way to do delete all the constraints followed by the tables themselves, using a concatenation trick involving FOR XML PATH('') which allows merging multiple input rows into a single output row. Should work on anything SQL 2005 & later.



                            I've left the EXECUTE commands commented out for safety.



                            DECLARE @SQL NVARCHAR(max)
                            ;WITH fkeys AS (
                            SELECT quotename(s.name) + '.' + quotename(o.name) tablename, quotename(fk.name) constraintname
                            FROM sys.foreign_keys fk
                            JOIN sys.objects o ON fk.parent_object_id = o.object_id
                            JOIN sys.schemas s ON o.schema_id = s.schema_id
                            )
                            SELECT @SQL = STUFF((SELECT '; ALTER TABLE ' + tablename + ' DROP CONSTRAINT ' + constraintname
                            FROM fkeys
                            FOR XML PATH('')),1,2,'')

                            -- EXECUTE(@sql)

                            SELECT @SQL = STUFF((SELECT '; DROP TABLE ' + quotename(TABLE_SCHEMA) + '.' + quotename(TABLE_NAME)
                            FROM INFORMATION_SCHEMA.TABLES
                            FOR XML PATH('')),1,2,'')

                            -- EXECUTE(@sql)





                            share|improve this answer
























                            • This doesn't work well because of not all FK will be dropped (only those where our tables is used as parent, where we should drop also constraint where our table was used as "referenced" ).

                              – Roman Pokrovskij
                              Dec 27 '13 at 10:05











                            • Roman, the original post is about deleting foreign key constraints. My answer doesn't aim to cover anything beyond that.

                              – Warren Rumak
                              Dec 27 '13 at 13:56
















                            1












                            1








                            1







                            Here's another way to do delete all the constraints followed by the tables themselves, using a concatenation trick involving FOR XML PATH('') which allows merging multiple input rows into a single output row. Should work on anything SQL 2005 & later.



                            I've left the EXECUTE commands commented out for safety.



                            DECLARE @SQL NVARCHAR(max)
                            ;WITH fkeys AS (
                            SELECT quotename(s.name) + '.' + quotename(o.name) tablename, quotename(fk.name) constraintname
                            FROM sys.foreign_keys fk
                            JOIN sys.objects o ON fk.parent_object_id = o.object_id
                            JOIN sys.schemas s ON o.schema_id = s.schema_id
                            )
                            SELECT @SQL = STUFF((SELECT '; ALTER TABLE ' + tablename + ' DROP CONSTRAINT ' + constraintname
                            FROM fkeys
                            FOR XML PATH('')),1,2,'')

                            -- EXECUTE(@sql)

                            SELECT @SQL = STUFF((SELECT '; DROP TABLE ' + quotename(TABLE_SCHEMA) + '.' + quotename(TABLE_NAME)
                            FROM INFORMATION_SCHEMA.TABLES
                            FOR XML PATH('')),1,2,'')

                            -- EXECUTE(@sql)





                            share|improve this answer













                            Here's another way to do delete all the constraints followed by the tables themselves, using a concatenation trick involving FOR XML PATH('') which allows merging multiple input rows into a single output row. Should work on anything SQL 2005 & later.



                            I've left the EXECUTE commands commented out for safety.



                            DECLARE @SQL NVARCHAR(max)
                            ;WITH fkeys AS (
                            SELECT quotename(s.name) + '.' + quotename(o.name) tablename, quotename(fk.name) constraintname
                            FROM sys.foreign_keys fk
                            JOIN sys.objects o ON fk.parent_object_id = o.object_id
                            JOIN sys.schemas s ON o.schema_id = s.schema_id
                            )
                            SELECT @SQL = STUFF((SELECT '; ALTER TABLE ' + tablename + ' DROP CONSTRAINT ' + constraintname
                            FROM fkeys
                            FOR XML PATH('')),1,2,'')

                            -- EXECUTE(@sql)

                            SELECT @SQL = STUFF((SELECT '; DROP TABLE ' + quotename(TABLE_SCHEMA) + '.' + quotename(TABLE_NAME)
                            FROM INFORMATION_SCHEMA.TABLES
                            FOR XML PATH('')),1,2,'')

                            -- EXECUTE(@sql)






                            share|improve this answer












                            share|improve this answer



                            share|improve this answer










                            answered Dec 12 '13 at 21:57









                            Warren RumakWarren Rumak

                            3,3621630




                            3,3621630













                            • This doesn't work well because of not all FK will be dropped (only those where our tables is used as parent, where we should drop also constraint where our table was used as "referenced" ).

                              – Roman Pokrovskij
                              Dec 27 '13 at 10:05











                            • Roman, the original post is about deleting foreign key constraints. My answer doesn't aim to cover anything beyond that.

                              – Warren Rumak
                              Dec 27 '13 at 13:56





















                            • This doesn't work well because of not all FK will be dropped (only those where our tables is used as parent, where we should drop also constraint where our table was used as "referenced" ).

                              – Roman Pokrovskij
                              Dec 27 '13 at 10:05











                            • Roman, the original post is about deleting foreign key constraints. My answer doesn't aim to cover anything beyond that.

                              – Warren Rumak
                              Dec 27 '13 at 13:56



















                            This doesn't work well because of not all FK will be dropped (only those where our tables is used as parent, where we should drop also constraint where our table was used as "referenced" ).

                            – Roman Pokrovskij
                            Dec 27 '13 at 10:05





                            This doesn't work well because of not all FK will be dropped (only those where our tables is used as parent, where we should drop also constraint where our table was used as "referenced" ).

                            – Roman Pokrovskij
                            Dec 27 '13 at 10:05













                            Roman, the original post is about deleting foreign key constraints. My answer doesn't aim to cover anything beyond that.

                            – Warren Rumak
                            Dec 27 '13 at 13:56







                            Roman, the original post is about deleting foreign key constraints. My answer doesn't aim to cover anything beyond that.

                            – Warren Rumak
                            Dec 27 '13 at 13:56













                            1














                            Here is a complete script to implement a solution:



                            create Procedure [dev].DeleteTablesFromSchema
                            (
                            @schemaName varchar(500)
                            )
                            As
                            begin
                            declare @constraintSchemaName nvarchar(128), @constraintTableName nvarchar(128), @constraintName nvarchar(128)
                            declare @sql nvarchar(max)
                            -- delete FK first
                            declare cur1 cursor for
                            select distinct
                            CASE WHEN t2.[object_id] is NOT NULL THEN s2.name ELSE s.name END as SchemaName,
                            CASE WHEN t2.[object_id] is NOT NULL THEN t2.name ELSE t.name END as TableName,
                            CASE WHEN t2.[object_id] is NOT NULL THEN OBJECT_NAME(d2.constraint_object_id) ELSE OBJECT_NAME(d.constraint_object_id) END as ConstraintName
                            from sys.objects t
                            inner join sys.schemas s
                            on t.[schema_id] = s.[schema_id]
                            left join sys.foreign_key_columns d
                            on d.parent_object_id = t.[object_id]
                            left join sys.foreign_key_columns d2
                            on d2.referenced_object_id = t.[object_id]
                            inner join sys.objects t2
                            on d2.parent_object_id = t2.[object_id]
                            inner join sys.schemas s2
                            on t2.[schema_id] = s2.[schema_id]
                            WHERE t.[type]='U'
                            AND t2.[type]='U'
                            AND t.is_ms_shipped = 0
                            AND t2.is_ms_shipped = 0
                            AND s.Name=@schemaName
                            open cur1
                            fetch next from cur1 into @constraintSchemaName, @constraintTableName, @constraintName
                            while @@fetch_status = 0
                            BEGIN
                            set @sql ='ALTER TABLE ' + @constraintSchemaName + '.' + @constraintTableName+' DROP CONSTRAINT '+@constraintName+';'
                            exec(@sql)
                            fetch next from cur1 into @constraintSchemaName, @constraintTableName, @constraintName
                            END
                            close cur1
                            deallocate cur1

                            DECLARE @tableName nvarchar(128)
                            declare cur2 cursor for
                            select s.Name, p.Name
                            from sys.objects p
                            INNER JOIN sys.schemas s ON p.[schema_id] = s.[schema_id]
                            WHERE p.[type]='U' and is_ms_shipped = 0
                            AND s.Name=@schemaName
                            ORDER BY s.Name, p.Name
                            open cur2

                            fetch next from cur2 into @schemaName,@tableName
                            while @@fetch_status = 0
                            begin
                            set @sql ='DROP TABLE ' + @schemaName + '.' + @tableName
                            exec(@sql)
                            fetch next from cur2 into @schemaName,@tableName
                            end

                            close cur2
                            deallocate cur2

                            end
                            go





                            share|improve this answer






























                              1














                              Here is a complete script to implement a solution:



                              create Procedure [dev].DeleteTablesFromSchema
                              (
                              @schemaName varchar(500)
                              )
                              As
                              begin
                              declare @constraintSchemaName nvarchar(128), @constraintTableName nvarchar(128), @constraintName nvarchar(128)
                              declare @sql nvarchar(max)
                              -- delete FK first
                              declare cur1 cursor for
                              select distinct
                              CASE WHEN t2.[object_id] is NOT NULL THEN s2.name ELSE s.name END as SchemaName,
                              CASE WHEN t2.[object_id] is NOT NULL THEN t2.name ELSE t.name END as TableName,
                              CASE WHEN t2.[object_id] is NOT NULL THEN OBJECT_NAME(d2.constraint_object_id) ELSE OBJECT_NAME(d.constraint_object_id) END as ConstraintName
                              from sys.objects t
                              inner join sys.schemas s
                              on t.[schema_id] = s.[schema_id]
                              left join sys.foreign_key_columns d
                              on d.parent_object_id = t.[object_id]
                              left join sys.foreign_key_columns d2
                              on d2.referenced_object_id = t.[object_id]
                              inner join sys.objects t2
                              on d2.parent_object_id = t2.[object_id]
                              inner join sys.schemas s2
                              on t2.[schema_id] = s2.[schema_id]
                              WHERE t.[type]='U'
                              AND t2.[type]='U'
                              AND t.is_ms_shipped = 0
                              AND t2.is_ms_shipped = 0
                              AND s.Name=@schemaName
                              open cur1
                              fetch next from cur1 into @constraintSchemaName, @constraintTableName, @constraintName
                              while @@fetch_status = 0
                              BEGIN
                              set @sql ='ALTER TABLE ' + @constraintSchemaName + '.' + @constraintTableName+' DROP CONSTRAINT '+@constraintName+';'
                              exec(@sql)
                              fetch next from cur1 into @constraintSchemaName, @constraintTableName, @constraintName
                              END
                              close cur1
                              deallocate cur1

                              DECLARE @tableName nvarchar(128)
                              declare cur2 cursor for
                              select s.Name, p.Name
                              from sys.objects p
                              INNER JOIN sys.schemas s ON p.[schema_id] = s.[schema_id]
                              WHERE p.[type]='U' and is_ms_shipped = 0
                              AND s.Name=@schemaName
                              ORDER BY s.Name, p.Name
                              open cur2

                              fetch next from cur2 into @schemaName,@tableName
                              while @@fetch_status = 0
                              begin
                              set @sql ='DROP TABLE ' + @schemaName + '.' + @tableName
                              exec(@sql)
                              fetch next from cur2 into @schemaName,@tableName
                              end

                              close cur2
                              deallocate cur2

                              end
                              go





                              share|improve this answer




























                                1












                                1








                                1







                                Here is a complete script to implement a solution:



                                create Procedure [dev].DeleteTablesFromSchema
                                (
                                @schemaName varchar(500)
                                )
                                As
                                begin
                                declare @constraintSchemaName nvarchar(128), @constraintTableName nvarchar(128), @constraintName nvarchar(128)
                                declare @sql nvarchar(max)
                                -- delete FK first
                                declare cur1 cursor for
                                select distinct
                                CASE WHEN t2.[object_id] is NOT NULL THEN s2.name ELSE s.name END as SchemaName,
                                CASE WHEN t2.[object_id] is NOT NULL THEN t2.name ELSE t.name END as TableName,
                                CASE WHEN t2.[object_id] is NOT NULL THEN OBJECT_NAME(d2.constraint_object_id) ELSE OBJECT_NAME(d.constraint_object_id) END as ConstraintName
                                from sys.objects t
                                inner join sys.schemas s
                                on t.[schema_id] = s.[schema_id]
                                left join sys.foreign_key_columns d
                                on d.parent_object_id = t.[object_id]
                                left join sys.foreign_key_columns d2
                                on d2.referenced_object_id = t.[object_id]
                                inner join sys.objects t2
                                on d2.parent_object_id = t2.[object_id]
                                inner join sys.schemas s2
                                on t2.[schema_id] = s2.[schema_id]
                                WHERE t.[type]='U'
                                AND t2.[type]='U'
                                AND t.is_ms_shipped = 0
                                AND t2.is_ms_shipped = 0
                                AND s.Name=@schemaName
                                open cur1
                                fetch next from cur1 into @constraintSchemaName, @constraintTableName, @constraintName
                                while @@fetch_status = 0
                                BEGIN
                                set @sql ='ALTER TABLE ' + @constraintSchemaName + '.' + @constraintTableName+' DROP CONSTRAINT '+@constraintName+';'
                                exec(@sql)
                                fetch next from cur1 into @constraintSchemaName, @constraintTableName, @constraintName
                                END
                                close cur1
                                deallocate cur1

                                DECLARE @tableName nvarchar(128)
                                declare cur2 cursor for
                                select s.Name, p.Name
                                from sys.objects p
                                INNER JOIN sys.schemas s ON p.[schema_id] = s.[schema_id]
                                WHERE p.[type]='U' and is_ms_shipped = 0
                                AND s.Name=@schemaName
                                ORDER BY s.Name, p.Name
                                open cur2

                                fetch next from cur2 into @schemaName,@tableName
                                while @@fetch_status = 0
                                begin
                                set @sql ='DROP TABLE ' + @schemaName + '.' + @tableName
                                exec(@sql)
                                fetch next from cur2 into @schemaName,@tableName
                                end

                                close cur2
                                deallocate cur2

                                end
                                go





                                share|improve this answer















                                Here is a complete script to implement a solution:



                                create Procedure [dev].DeleteTablesFromSchema
                                (
                                @schemaName varchar(500)
                                )
                                As
                                begin
                                declare @constraintSchemaName nvarchar(128), @constraintTableName nvarchar(128), @constraintName nvarchar(128)
                                declare @sql nvarchar(max)
                                -- delete FK first
                                declare cur1 cursor for
                                select distinct
                                CASE WHEN t2.[object_id] is NOT NULL THEN s2.name ELSE s.name END as SchemaName,
                                CASE WHEN t2.[object_id] is NOT NULL THEN t2.name ELSE t.name END as TableName,
                                CASE WHEN t2.[object_id] is NOT NULL THEN OBJECT_NAME(d2.constraint_object_id) ELSE OBJECT_NAME(d.constraint_object_id) END as ConstraintName
                                from sys.objects t
                                inner join sys.schemas s
                                on t.[schema_id] = s.[schema_id]
                                left join sys.foreign_key_columns d
                                on d.parent_object_id = t.[object_id]
                                left join sys.foreign_key_columns d2
                                on d2.referenced_object_id = t.[object_id]
                                inner join sys.objects t2
                                on d2.parent_object_id = t2.[object_id]
                                inner join sys.schemas s2
                                on t2.[schema_id] = s2.[schema_id]
                                WHERE t.[type]='U'
                                AND t2.[type]='U'
                                AND t.is_ms_shipped = 0
                                AND t2.is_ms_shipped = 0
                                AND s.Name=@schemaName
                                open cur1
                                fetch next from cur1 into @constraintSchemaName, @constraintTableName, @constraintName
                                while @@fetch_status = 0
                                BEGIN
                                set @sql ='ALTER TABLE ' + @constraintSchemaName + '.' + @constraintTableName+' DROP CONSTRAINT '+@constraintName+';'
                                exec(@sql)
                                fetch next from cur1 into @constraintSchemaName, @constraintTableName, @constraintName
                                END
                                close cur1
                                deallocate cur1

                                DECLARE @tableName nvarchar(128)
                                declare cur2 cursor for
                                select s.Name, p.Name
                                from sys.objects p
                                INNER JOIN sys.schemas s ON p.[schema_id] = s.[schema_id]
                                WHERE p.[type]='U' and is_ms_shipped = 0
                                AND s.Name=@schemaName
                                ORDER BY s.Name, p.Name
                                open cur2

                                fetch next from cur2 into @schemaName,@tableName
                                while @@fetch_status = 0
                                begin
                                set @sql ='DROP TABLE ' + @schemaName + '.' + @tableName
                                exec(@sql)
                                fetch next from cur2 into @schemaName,@tableName
                                end

                                close cur2
                                deallocate cur2

                                end
                                go






                                share|improve this answer














                                share|improve this answer



                                share|improve this answer








                                edited May 11 '15 at 19:27









                                David Hoelzer

                                11.6k42752




                                11.6k42752










                                answered Dec 27 '13 at 10:06









                                Roman PokrovskijRoman Pokrovskij

                                4,31895080




                                4,31895080























                                    0















                                    If I want to delete all the tables in
                                    my database




                                    Then it's a lot easier to drop the entire database:



                                    DROP DATABASE WorkerPensions





                                    share|improve this answer



















                                    • 63





                                      Giving you a -1 for this as it is not a valid answer to the question, for two important reasons: 1) It deletes a lot more than tables! Stored procedures, functions, UDTs, security, .NET assemblies, etc. all go away with a DROP DATABASE. 2) You may not be allowed to create databases, e.g. centrally-managed dev environment where databases are provisioned by IT and have additional requirements at create-time that you aren't aware of.

                                      – Warren Rumak
                                      Dec 12 '13 at 21:30


















                                    0















                                    If I want to delete all the tables in
                                    my database




                                    Then it's a lot easier to drop the entire database:



                                    DROP DATABASE WorkerPensions





                                    share|improve this answer



















                                    • 63





                                      Giving you a -1 for this as it is not a valid answer to the question, for two important reasons: 1) It deletes a lot more than tables! Stored procedures, functions, UDTs, security, .NET assemblies, etc. all go away with a DROP DATABASE. 2) You may not be allowed to create databases, e.g. centrally-managed dev environment where databases are provisioned by IT and have additional requirements at create-time that you aren't aware of.

                                      – Warren Rumak
                                      Dec 12 '13 at 21:30
















                                    0












                                    0








                                    0








                                    If I want to delete all the tables in
                                    my database




                                    Then it's a lot easier to drop the entire database:



                                    DROP DATABASE WorkerPensions





                                    share|improve this answer














                                    If I want to delete all the tables in
                                    my database




                                    Then it's a lot easier to drop the entire database:



                                    DROP DATABASE WorkerPensions






                                    share|improve this answer












                                    share|improve this answer



                                    share|improve this answer










                                    answered Nov 21 '09 at 18:03









                                    AndomarAndomar

                                    190k34291337




                                    190k34291337








                                    • 63





                                      Giving you a -1 for this as it is not a valid answer to the question, for two important reasons: 1) It deletes a lot more than tables! Stored procedures, functions, UDTs, security, .NET assemblies, etc. all go away with a DROP DATABASE. 2) You may not be allowed to create databases, e.g. centrally-managed dev environment where databases are provisioned by IT and have additional requirements at create-time that you aren't aware of.

                                      – Warren Rumak
                                      Dec 12 '13 at 21:30
















                                    • 63





                                      Giving you a -1 for this as it is not a valid answer to the question, for two important reasons: 1) It deletes a lot more than tables! Stored procedures, functions, UDTs, security, .NET assemblies, etc. all go away with a DROP DATABASE. 2) You may not be allowed to create databases, e.g. centrally-managed dev environment where databases are provisioned by IT and have additional requirements at create-time that you aren't aware of.

                                      – Warren Rumak
                                      Dec 12 '13 at 21:30










                                    63




                                    63





                                    Giving you a -1 for this as it is not a valid answer to the question, for two important reasons: 1) It deletes a lot more than tables! Stored procedures, functions, UDTs, security, .NET assemblies, etc. all go away with a DROP DATABASE. 2) You may not be allowed to create databases, e.g. centrally-managed dev environment where databases are provisioned by IT and have additional requirements at create-time that you aren't aware of.

                                    – Warren Rumak
                                    Dec 12 '13 at 21:30







                                    Giving you a -1 for this as it is not a valid answer to the question, for two important reasons: 1) It deletes a lot more than tables! Stored procedures, functions, UDTs, security, .NET assemblies, etc. all go away with a DROP DATABASE. 2) You may not be allowed to create databases, e.g. centrally-managed dev environment where databases are provisioned by IT and have additional requirements at create-time that you aren't aware of.

                                    – Warren Rumak
                                    Dec 12 '13 at 21:30













                                    0














                                    Removing Referenced FOREIGN KEY Constraints
                                    Assuming there is a parent and child table Relationship in SQL Server:

                                    --First find the name of the Foreign Key Constraint:
                                    SELECT *
                                    FROM sys.foreign_keys
                                    WHERE referenced_object_id = object_id('States')

                                    --Then Find foreign keys referencing to dbo.Parent(States) table:
                                    SELECT name AS 'Foreign Key Constraint Name',
                                    OBJECT_SCHEMA_NAME(parent_object_id) + '.' + OBJECT_NAME(parent_object_id) AS 'Child Table'
                                    FROM sys.foreign_keys
                                    WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'dbo' AND
                                    OBJECT_NAME(referenced_object_id) = 'dbo.State'

                                    -- Drop the foreign key constraint by its name
                                    ALTER TABLE dbo.cities DROP CONSTRAINT FK__cities__state__6442E2C9;

                                    -- You can also use the following T-SQL script to automatically find
                                    --and drop all foreign key constraints referencing to the specified parent
                                    -- table:

                                    BEGIN

                                    DECLARE @stmt VARCHAR(300);

                                    -- Cursor to generate ALTER TABLE DROP CONSTRAINT statements
                                    DECLARE cur CURSOR FOR
                                    SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.' +
                                    OBJECT_NAME(parent_object_id) +
                                    ' DROP CONSTRAINT ' + name
                                    FROM sys.foreign_keys
                                    WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'dbo' AND
                                    OBJECT_NAME(referenced_object_id) = 'states';

                                    OPEN cur;
                                    FETCH cur INTO @stmt;

                                    -- Drop each found foreign key constraint
                                    WHILE @@FETCH_STATUS = 0
                                    BEGIN
                                    EXEC (@stmt);
                                    FETCH cur INTO @stmt;
                                    END

                                    CLOSE cur;
                                    DEALLOCATE cur;

                                    END
                                    GO

                                    --Now you can drop the parent table:

                                    DROP TABLE states;
                                    --# Command(s) completed successfully.





                                    share|improve this answer






























                                      0














                                      Removing Referenced FOREIGN KEY Constraints
                                      Assuming there is a parent and child table Relationship in SQL Server:

                                      --First find the name of the Foreign Key Constraint:
                                      SELECT *
                                      FROM sys.foreign_keys
                                      WHERE referenced_object_id = object_id('States')

                                      --Then Find foreign keys referencing to dbo.Parent(States) table:
                                      SELECT name AS 'Foreign Key Constraint Name',
                                      OBJECT_SCHEMA_NAME(parent_object_id) + '.' + OBJECT_NAME(parent_object_id) AS 'Child Table'
                                      FROM sys.foreign_keys
                                      WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'dbo' AND
                                      OBJECT_NAME(referenced_object_id) = 'dbo.State'

                                      -- Drop the foreign key constraint by its name
                                      ALTER TABLE dbo.cities DROP CONSTRAINT FK__cities__state__6442E2C9;

                                      -- You can also use the following T-SQL script to automatically find
                                      --and drop all foreign key constraints referencing to the specified parent
                                      -- table:

                                      BEGIN

                                      DECLARE @stmt VARCHAR(300);

                                      -- Cursor to generate ALTER TABLE DROP CONSTRAINT statements
                                      DECLARE cur CURSOR FOR
                                      SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.' +
                                      OBJECT_NAME(parent_object_id) +
                                      ' DROP CONSTRAINT ' + name
                                      FROM sys.foreign_keys
                                      WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'dbo' AND
                                      OBJECT_NAME(referenced_object_id) = 'states';

                                      OPEN cur;
                                      FETCH cur INTO @stmt;

                                      -- Drop each found foreign key constraint
                                      WHILE @@FETCH_STATUS = 0
                                      BEGIN
                                      EXEC (@stmt);
                                      FETCH cur INTO @stmt;
                                      END

                                      CLOSE cur;
                                      DEALLOCATE cur;

                                      END
                                      GO

                                      --Now you can drop the parent table:

                                      DROP TABLE states;
                                      --# Command(s) completed successfully.





                                      share|improve this answer




























                                        0












                                        0








                                        0







                                        Removing Referenced FOREIGN KEY Constraints
                                        Assuming there is a parent and child table Relationship in SQL Server:

                                        --First find the name of the Foreign Key Constraint:
                                        SELECT *
                                        FROM sys.foreign_keys
                                        WHERE referenced_object_id = object_id('States')

                                        --Then Find foreign keys referencing to dbo.Parent(States) table:
                                        SELECT name AS 'Foreign Key Constraint Name',
                                        OBJECT_SCHEMA_NAME(parent_object_id) + '.' + OBJECT_NAME(parent_object_id) AS 'Child Table'
                                        FROM sys.foreign_keys
                                        WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'dbo' AND
                                        OBJECT_NAME(referenced_object_id) = 'dbo.State'

                                        -- Drop the foreign key constraint by its name
                                        ALTER TABLE dbo.cities DROP CONSTRAINT FK__cities__state__6442E2C9;

                                        -- You can also use the following T-SQL script to automatically find
                                        --and drop all foreign key constraints referencing to the specified parent
                                        -- table:

                                        BEGIN

                                        DECLARE @stmt VARCHAR(300);

                                        -- Cursor to generate ALTER TABLE DROP CONSTRAINT statements
                                        DECLARE cur CURSOR FOR
                                        SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.' +
                                        OBJECT_NAME(parent_object_id) +
                                        ' DROP CONSTRAINT ' + name
                                        FROM sys.foreign_keys
                                        WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'dbo' AND
                                        OBJECT_NAME(referenced_object_id) = 'states';

                                        OPEN cur;
                                        FETCH cur INTO @stmt;

                                        -- Drop each found foreign key constraint
                                        WHILE @@FETCH_STATUS = 0
                                        BEGIN
                                        EXEC (@stmt);
                                        FETCH cur INTO @stmt;
                                        END

                                        CLOSE cur;
                                        DEALLOCATE cur;

                                        END
                                        GO

                                        --Now you can drop the parent table:

                                        DROP TABLE states;
                                        --# Command(s) completed successfully.





                                        share|improve this answer















                                        Removing Referenced FOREIGN KEY Constraints
                                        Assuming there is a parent and child table Relationship in SQL Server:

                                        --First find the name of the Foreign Key Constraint:
                                        SELECT *
                                        FROM sys.foreign_keys
                                        WHERE referenced_object_id = object_id('States')

                                        --Then Find foreign keys referencing to dbo.Parent(States) table:
                                        SELECT name AS 'Foreign Key Constraint Name',
                                        OBJECT_SCHEMA_NAME(parent_object_id) + '.' + OBJECT_NAME(parent_object_id) AS 'Child Table'
                                        FROM sys.foreign_keys
                                        WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'dbo' AND
                                        OBJECT_NAME(referenced_object_id) = 'dbo.State'

                                        -- Drop the foreign key constraint by its name
                                        ALTER TABLE dbo.cities DROP CONSTRAINT FK__cities__state__6442E2C9;

                                        -- You can also use the following T-SQL script to automatically find
                                        --and drop all foreign key constraints referencing to the specified parent
                                        -- table:

                                        BEGIN

                                        DECLARE @stmt VARCHAR(300);

                                        -- Cursor to generate ALTER TABLE DROP CONSTRAINT statements
                                        DECLARE cur CURSOR FOR
                                        SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.' +
                                        OBJECT_NAME(parent_object_id) +
                                        ' DROP CONSTRAINT ' + name
                                        FROM sys.foreign_keys
                                        WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'dbo' AND
                                        OBJECT_NAME(referenced_object_id) = 'states';

                                        OPEN cur;
                                        FETCH cur INTO @stmt;

                                        -- Drop each found foreign key constraint
                                        WHILE @@FETCH_STATUS = 0
                                        BEGIN
                                        EXEC (@stmt);
                                        FETCH cur INTO @stmt;
                                        END

                                        CLOSE cur;
                                        DEALLOCATE cur;

                                        END
                                        GO

                                        --Now you can drop the parent table:

                                        DROP TABLE states;
                                        --# Command(s) completed successfully.






                                        share|improve this answer














                                        share|improve this answer



                                        share|improve this answer








                                        edited Oct 23 '17 at 5:36

























                                        answered Oct 23 '17 at 5:28









                                        CodeTzuCodeTzu

                                        12




                                        12























                                            0














                                            Using SQL Server Manager you can drop foreign key constraints from the UI. If you want to delete the table Diary but the User table has a foreign key DiaryId pointing to the Diary table, you can expand (using the plus symbol) the User table and then expand the Foreign Keys section. Right click on the foreign key that points to the diary table then select Delete. You can then expand the Columns section, right click and delete the column DiaryId too. Then you can just run:



                                            drop table Diary




                                            I know your actual question is about deleting all tables, so this may not be a useful for that case. However, if you just want to delete a few tables this is useful I believe (the title does not explicitly mention deleting all tables).






                                            share|improve this answer






























                                              0














                                              Using SQL Server Manager you can drop foreign key constraints from the UI. If you want to delete the table Diary but the User table has a foreign key DiaryId pointing to the Diary table, you can expand (using the plus symbol) the User table and then expand the Foreign Keys section. Right click on the foreign key that points to the diary table then select Delete. You can then expand the Columns section, right click and delete the column DiaryId too. Then you can just run:



                                              drop table Diary




                                              I know your actual question is about deleting all tables, so this may not be a useful for that case. However, if you just want to delete a few tables this is useful I believe (the title does not explicitly mention deleting all tables).






                                              share|improve this answer




























                                                0












                                                0








                                                0







                                                Using SQL Server Manager you can drop foreign key constraints from the UI. If you want to delete the table Diary but the User table has a foreign key DiaryId pointing to the Diary table, you can expand (using the plus symbol) the User table and then expand the Foreign Keys section. Right click on the foreign key that points to the diary table then select Delete. You can then expand the Columns section, right click and delete the column DiaryId too. Then you can just run:



                                                drop table Diary




                                                I know your actual question is about deleting all tables, so this may not be a useful for that case. However, if you just want to delete a few tables this is useful I believe (the title does not explicitly mention deleting all tables).






                                                share|improve this answer















                                                Using SQL Server Manager you can drop foreign key constraints from the UI. If you want to delete the table Diary but the User table has a foreign key DiaryId pointing to the Diary table, you can expand (using the plus symbol) the User table and then expand the Foreign Keys section. Right click on the foreign key that points to the diary table then select Delete. You can then expand the Columns section, right click and delete the column DiaryId too. Then you can just run:



                                                drop table Diary




                                                I know your actual question is about deleting all tables, so this may not be a useful for that case. However, if you just want to delete a few tables this is useful I believe (the title does not explicitly mention deleting all tables).







                                                share|improve this answer














                                                share|improve this answer



                                                share|improve this answer








                                                edited Nov 8 '18 at 12:30

























                                                answered Nov 8 '18 at 12:25









                                                HazzaHazza

                                                4,90821731




                                                4,90821731























                                                    -1














                                                    If you are on a mysql server and if you don't mind loosing your tables, you can use a simple query to delete multiple tables at once:



                                                    SET foreign_key_checks = 0;
                                                    DROP TABLE IF EXISTS table_a,table_b,table_c,table_etc;
                                                    SET foreign_key_checks = 1;


                                                    In this way it doesn't matter in what order you use the table in you query.



                                                    If anybody is going to say something about the fact that this is not a good solution if you have a database with many tables: I agree!






                                                    share|improve this answer


























                                                    • I get Incorrect syntax near '='. (102) (SQLExecDirectW)

                                                      – Matt
                                                      Aug 7 '16 at 22:48











                                                    • @Matt Bit hard to guess at which '=' you get that error.

                                                      – Els den Iep
                                                      Aug 8 '16 at 14:17






                                                    • 2





                                                      foreign_key_checks will not work on MSSQL server. I think that's a MySql specific variable.

                                                      – ooXei1sh
                                                      Oct 11 '16 at 16:33
















                                                    -1














                                                    If you are on a mysql server and if you don't mind loosing your tables, you can use a simple query to delete multiple tables at once:



                                                    SET foreign_key_checks = 0;
                                                    DROP TABLE IF EXISTS table_a,table_b,table_c,table_etc;
                                                    SET foreign_key_checks = 1;


                                                    In this way it doesn't matter in what order you use the table in you query.



                                                    If anybody is going to say something about the fact that this is not a good solution if you have a database with many tables: I agree!






                                                    share|improve this answer


























                                                    • I get Incorrect syntax near '='. (102) (SQLExecDirectW)

                                                      – Matt
                                                      Aug 7 '16 at 22:48











                                                    • @Matt Bit hard to guess at which '=' you get that error.

                                                      – Els den Iep
                                                      Aug 8 '16 at 14:17






                                                    • 2





                                                      foreign_key_checks will not work on MSSQL server. I think that's a MySql specific variable.

                                                      – ooXei1sh
                                                      Oct 11 '16 at 16:33














                                                    -1












                                                    -1








                                                    -1







                                                    If you are on a mysql server and if you don't mind loosing your tables, you can use a simple query to delete multiple tables at once:



                                                    SET foreign_key_checks = 0;
                                                    DROP TABLE IF EXISTS table_a,table_b,table_c,table_etc;
                                                    SET foreign_key_checks = 1;


                                                    In this way it doesn't matter in what order you use the table in you query.



                                                    If anybody is going to say something about the fact that this is not a good solution if you have a database with many tables: I agree!






                                                    share|improve this answer















                                                    If you are on a mysql server and if you don't mind loosing your tables, you can use a simple query to delete multiple tables at once:



                                                    SET foreign_key_checks = 0;
                                                    DROP TABLE IF EXISTS table_a,table_b,table_c,table_etc;
                                                    SET foreign_key_checks = 1;


                                                    In this way it doesn't matter in what order you use the table in you query.



                                                    If anybody is going to say something about the fact that this is not a good solution if you have a database with many tables: I agree!







                                                    share|improve this answer














                                                    share|improve this answer



                                                    share|improve this answer








                                                    edited Nov 19 '18 at 9:33

























                                                    answered Jul 19 '16 at 16:07









                                                    Els den IepEls den Iep

                                                    190111




                                                    190111













                                                    • I get Incorrect syntax near '='. (102) (SQLExecDirectW)

                                                      – Matt
                                                      Aug 7 '16 at 22:48











                                                    • @Matt Bit hard to guess at which '=' you get that error.

                                                      – Els den Iep
                                                      Aug 8 '16 at 14:17






                                                    • 2





                                                      foreign_key_checks will not work on MSSQL server. I think that's a MySql specific variable.

                                                      – ooXei1sh
                                                      Oct 11 '16 at 16:33



















                                                    • I get Incorrect syntax near '='. (102) (SQLExecDirectW)

                                                      – Matt
                                                      Aug 7 '16 at 22:48











                                                    • @Matt Bit hard to guess at which '=' you get that error.

                                                      – Els den Iep
                                                      Aug 8 '16 at 14:17






                                                    • 2





                                                      foreign_key_checks will not work on MSSQL server. I think that's a MySql specific variable.

                                                      – ooXei1sh
                                                      Oct 11 '16 at 16:33

















                                                    I get Incorrect syntax near '='. (102) (SQLExecDirectW)

                                                    – Matt
                                                    Aug 7 '16 at 22:48





                                                    I get Incorrect syntax near '='. (102) (SQLExecDirectW)

                                                    – Matt
                                                    Aug 7 '16 at 22:48













                                                    @Matt Bit hard to guess at which '=' you get that error.

                                                    – Els den Iep
                                                    Aug 8 '16 at 14:17





                                                    @Matt Bit hard to guess at which '=' you get that error.

                                                    – Els den Iep
                                                    Aug 8 '16 at 14:17




                                                    2




                                                    2





                                                    foreign_key_checks will not work on MSSQL server. I think that's a MySql specific variable.

                                                    – ooXei1sh
                                                    Oct 11 '16 at 16:33





                                                    foreign_key_checks will not work on MSSQL server. I think that's a MySql specific variable.

                                                    – ooXei1sh
                                                    Oct 11 '16 at 16:33











                                                    -3














                                                    If you want to DROP a table which has been referenced by other table using the foreign key use



                                                    DROP TABLE *table_name* CASCADE CONSTRAINTS;

                                                    I think it should work for you.






                                                    share|improve this answer





















                                                    • 1





                                                      there is no cascade constraints in sql server

                                                      – believesInSanta
                                                      Feb 15 '16 at 13:39
















                                                    -3














                                                    If you want to DROP a table which has been referenced by other table using the foreign key use



                                                    DROP TABLE *table_name* CASCADE CONSTRAINTS;

                                                    I think it should work for you.






                                                    share|improve this answer





















                                                    • 1





                                                      there is no cascade constraints in sql server

                                                      – believesInSanta
                                                      Feb 15 '16 at 13:39














                                                    -3












                                                    -3








                                                    -3







                                                    If you want to DROP a table which has been referenced by other table using the foreign key use



                                                    DROP TABLE *table_name* CASCADE CONSTRAINTS;

                                                    I think it should work for you.






                                                    share|improve this answer















                                                    If you want to DROP a table which has been referenced by other table using the foreign key use



                                                    DROP TABLE *table_name* CASCADE CONSTRAINTS;

                                                    I think it should work for you.







                                                    share|improve this answer














                                                    share|improve this answer



                                                    share|improve this answer








                                                    edited Dec 4 '15 at 7:21









                                                    Musakkhir Sayyed

                                                    4,03772956




                                                    4,03772956










                                                    answered Dec 4 '15 at 6:36









                                                    NitishNitish

                                                    1




                                                    1








                                                    • 1





                                                      there is no cascade constraints in sql server

                                                      – believesInSanta
                                                      Feb 15 '16 at 13:39














                                                    • 1





                                                      there is no cascade constraints in sql server

                                                      – believesInSanta
                                                      Feb 15 '16 at 13:39








                                                    1




                                                    1





                                                    there is no cascade constraints in sql server

                                                    – believesInSanta
                                                    Feb 15 '16 at 13:39





                                                    there is no cascade constraints in sql server

                                                    – believesInSanta
                                                    Feb 15 '16 at 13:39


















                                                    draft saved

                                                    draft discarded




















































                                                    Thanks for contributing an answer to Stack Overflow!


                                                    • Please be sure to answer the question. Provide details and share your research!

                                                    But avoid



                                                    • Asking for help, clarification, or responding to other answers.

                                                    • Making statements based on opinion; back them up with references or personal experience.


                                                    To learn more, see our tips on writing great answers.




                                                    draft saved


                                                    draft discarded














                                                    StackExchange.ready(
                                                    function () {
                                                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f1776079%2fsql-drop-table-foreign-key-constraint%23new-answer', 'question_page');
                                                    }
                                                    );

                                                    Post as a guest















                                                    Required, but never shown





















































                                                    Required, but never shown














                                                    Required, but never shown












                                                    Required, but never shown







                                                    Required, but never shown

































                                                    Required, but never shown














                                                    Required, but never shown












                                                    Required, but never shown







                                                    Required, but never shown







                                                    Popular posts from this blog

                                                    Guess what letter conforming each word

                                                    Port of Spain

                                                    Run scheduled task as local user group (not BUILTIN)