T-SQL Drop all tables

Question

I am trying to drop all tables in a database without having to do it in the proper order. From what I have read running the NOCHECK command will prevent foreign keys from being checked. However, even after running that I still get an error trying to drop the first table.

Could not drop object 'dbo.TABLENAME' because it is referenced by a FOREIGN KEY constraint

I have seen this question answered successfully before so I don't understand what is different with what I am doing. This is running on SQL Server 2008 R2.

BEGIN TRANSACTION

--get current list of tables
SELECT QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) as 'Dropped Table'
FROM   sys.tables t
       JOIN sys.schemas s
         ON t.[schema_id] = s.[schema_id]
WHERE  t.type = 'U'


--disable constraint checking in all tables
DECLARE @sql NVARCHAR(max)
SET @sql = ''
SELECT @sql += ' ALTER TABLE ' + QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) + ' NOCHECK CONSTRAINT ALL; '
FROM   sys.tables t
       JOIN sys.schemas s
         ON t.[schema_id] = s.[schema_id]
WHERE  t.type = 'U'
select @sql
Exec sp_executesql @sql

--disable all constraints (this also didn't work)
--EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"


--drop all tables
SET @sql = ''
SELECT @sql += ' DROP TABLE ' + QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) + '; '
FROM   sys.tables t
       JOIN sys.schemas s
         ON t.[schema_id] = s.[schema_id]
WHERE  t.type = 'U'
select @sql
Exec sp_executesql @sql


--check current list, should be empty
SELECT QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) as 'Tables'
FROM   sys.tables t
       JOIN sys.schemas s
         ON t.[schema_id] = s.[schema_id]
WHERE  t.type = 'U'

ROLLBACK TRANSACTION

Update 1

I removed the constraint disabling code in place of constraint dropping code but it gives and error.

--drop all constraints
DECLARE @sql NVARCHAR(max)
SET @sql = ''
SELECT @sql += ' ALTER TABLE ' +QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) + ' DROP CONSTRAINT ' + ctu.CONSTRAINT_NAME + ';'
FROM sys.tables t
    JOIN sys.schemas s
        ON t.[schema_id] = s.[schema_id]
    INNER JOIN EOS_DEV.INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE as ctu
        ON ctu.TABLE_SCHEMA = s.name AND ctu.TABLE_NAME = t.name
WHERE  t.type = 'U'
Exec sp_executesql @sql

The constraint '[CONSTRAINT_NAME]' is being referenced by table '[TABLE_NAME]', foreign key constraint '[FK_NAME]'

How can I modify this query so I only target FK constraints?

I would give a try by dropping all the constraints first and then try dropping tables. — Praveen, Jun 14 '17 at 14:22
@Praveen isn't the entire question how to drop the tables using NOCHECK without dropping the constraints? I think the user is aware that they can drop the constraints first... — Jacob H, Jun 14 '17 at 14:24
Have you tried with just one table? Run the `exec sp_MSforeachtable @command1='alter table ? nocheck constraint all'` and drop one table. It should work. — Jacob H, Jun 14 '17 at 14:30
I would try setting a loop to keep running the exec sp_MSforeachtable 'Drop Table ?' that check for an error return of 0. Each iteration will drop the referencing tables. Never actually written the loop but usually if I want to drop all tables I just execute that command over and over and eventually they all get dropped. — Anthony Hancock, Jun 14 '17 at 14:53

score 1 · Accepted Answer · answered Jun 16 '17 at 14:43

Thanks everyone for your help. I updated my query and can now confirm that it has the ability to drop all tables indiscriminately. I also added a section to drop all stored procs for a little added flavor.

BEGIN TRANSACTION

--get current list of tables
SELECT QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) as 'Dropped Table'
FROM   sys.tables t
       JOIN sys.schemas s
         ON t.[schema_id] = s.[schema_id]
WHERE  t.type = 'U'

--drop all constraints
DECLARE @sql NVARCHAR(max)
SET @sql = ''
SELECT @sql += ' ALTER TABLE ' +QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) + ' DROP CONSTRAINT ' + tc.CONSTRAINT_NAME + ';'
FROM sys.tables t
    JOIN sys.schemas s
        ON t.[schema_id] = s.[schema_id]
    INNER JOIN EOS_DEV.INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
        ON tc.TABLE_SCHEMA = s.name AND tc.TABLE_NAME = t.name
WHERE t.type = 'U'
    AND tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
Exec sp_executesql @sql

--drop all tables
SET @sql = ''
SELECT @sql += ' DROP TABLE ' + QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) + '; '
FROM   sys.tables t
       JOIN sys.schemas s
         ON t.[schema_id] = s.[schema_id]
WHERE  t.type = 'U'
Exec sp_executesql @sql

--drop all stored procs
SET @sql = ''
SELECT @sql += 'DROP PROCEDURE [' + SCHEMA_NAME(p.schema_id) + '].[' + p.NAME + ']'
FROM sys.procedures as p 
where p.is_ms_shipped = 0
    AND p.type = 'P'
Exec sp_executesql @sql

ROLLBACK TRANSACTION

score -1 · Answer 2 · answered Jun 14 '17 at 14:34

Setting a FK to NOCHECK will allow you to INSERT,UPDATE, or DELETE rows that would violate the constraint. It will not allow you to DROP or TRUNCATE the target table. EG:

use tempdb

create table a(id int primary key)

create table b(id int primary key, aid int references a)

alter table b nocheck constraint all

insert into b(id,aid) values (1,1) --succeeds because of nocheck

drop table a --fails
--Msg 3726, Level 16, State 1, Line 11
--Could not drop object 'a' because it is referenced by a FOREIGN KEY constraint.

Thanks for clarifying what is going wrong but this does not present a solution. — Swazimodo, Jun 15 '17 at 18:34

score -1 · Answer 3 · answered Jun 14 '17 at 15:02

-1

Do you have to make this so difficult? Why not just restore an empty database (or a database that contains just your schema and whatever "default" rows are needed)?

answered Jun 14 '17 at 15:02

SMor

2,830
4
11
14

I lack access and don't want to write up a formal implementation plan for DEV. – Swazimodo Jun 15 '17 at 18:32

T-SQL Drop all tables

3 Answers3