How to drop all the tables from a database in SQL Server?

How to drop all the tables from a database in SQL Server?

by

Usually if you want to drop all the tables from an SQL Server database, you can just drop the database and create a new database. However, in rare situations like database level permission issues, you may need to drop all the tables from a SQL Server database and recreate them.

How to drop all the tables from a database in SQL Server?

There are few ways to do this. Let us see one of the methods I use frequently.

To drop all the tables, you have to identify the constraints and the references of the tables by other objects and remove them first.

  1. So the first step is to identify the references of the user defined functions in the database. The possibility of UDFs referencing other objects like stored procedures or Security Policies are very slim. Still we have to consider them. So, the first step is to drop the Security Policies.
  2. Next, drop all the Stored Procedures and the User Defined Functions. You can use INFORMATION_SCHEMA.ROUTINES to find and drop the UDFs and Procedures.
  3. Then, disable system versioning of temporal tables. This is needed, because you will not be able to drop the tables when system versioning is active.
  4. Then, remove all the Constraints from the tables. We can automate this process by creating ALTER TABLE … DROP Constraint, statement using INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS and INFORMATION_SCHEMA.TABLE_CONSTRAINTS.
  5. Finally, we can drop all the tables using sp_MSForEachTable.

Here is the SQL statement I’m using to drop all the tables and their references from a database.

SQL statement to drop all the tables from a database

Method 1 (considering System versioned Temporal Tables):

This is the current SQL script I’m using to remove the tables from the database. This sample script uses Microsoft’s sample database WideWorldImporters.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
USE WideWorldImporters
 
/* Drop Security Policy */
DECLARE @sql NVARCHAR(MAX)
SELECT
    @sql = STUFF((SELECT N'; ' + N'DROP SECURITY POLICY ' + name
FROM sys.objects
WHERE type_desc = 'SECURITY_POLICY'
FOR XML PATH('')),1,1,'')
 
EXECUTE (@sql)
GO
 
/* Drop Procedures */
DECLARE @sql_1 NVARCHAR(MAX)
SELECT
    @sql_1 = STUFF((SELECT N'; ' +  N'DROP PROCEDURE [' +
    SPECIFIC_SCHEMA  + N'].[' + SPECIFIC_NAME + N']' 
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
FOR XML PATH('')),1,1,'')
 
EXECUTE (@sql_1)
GO
 
/* Drop  User Defined Functions */
DECLARE @sql_2 NVARCHAR(MAX)
SELECT
    @sql_2 = STUFF((SELECT N'; ' + N'DROP FUNCTION [' +
    SPECIFIC_SCHEMA  + N'].[' + SPECIFIC_NAME + N']' 
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION'
FOR XML PATH('')),1,1,'')
 
EXECUTE (@sql_2)
GO
 
/* Disable system versioning (temporal tables) */
EXEC sp_MSForEachTable
    'IF OBJECTPROPERTY(object_id(''?''), ''TableTemporalType'') = 2
    ALTER TABLE ? SET (SYSTEM_VERSIONING = OFF)'
GO
 
/* Disable constraints (foreign keys) */
DECLARE @sql_3 NVARCHAR(MAX)
SELECT
    @sql_3 = STUFF((SELECT N'; ' + N'ALTER TABLE [' +
        tab_con.TABLE_SCHEMA + N'].[' + 
        tab_con.TABLE_NAME + N'] DROP [' +
        ref_con.CONSTRAINT_NAME + N'] '
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ref_con
    LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tab_con
        ON tab_con.CONSTRAINT_NAME = ref_con.CONSTRAINT_NAME
FOR XML PATH('')),1,1,'')
 
EXECUTE (@sql_3)
GO
 
/* Drop tables */
EXEC sp_MSForEachTable 'DROP TABLE ?'
GO

Method 2:

Here is the old method which I’ve used before. This method does not consider system versioned tables (temporal tables). Otherwise, it’s similar to the first method. Here I’ve used cursors.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
/* Drop constraints */
DECLARE @SQL NVARCHAR(MAX)
DECLARE @CURSOR CURSOR
 
SET @CURSOR = CURSOR FAST_FORWARD FOR
    SELECT
        DISTINCT sql = N'ALTER TABLE [' +
            tab_con.TABLE_SCHEMA + N'].[' + 
            tab_con.TABLE_NAME + N'] DROP [' +
            ref_con.CONSTRAINT_NAME + N'];'
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ref_con
        LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tab_con
            ON tab_con.CONSTRAINT_NAME = ref_con.CONSTRAINT_NAME
 
OPEN @CURSOR FETCH NEXT FROM @CURSOR INTO @SQL
 
WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC SP_EXECUTESQL @SQL
    FETCH NEXT FROM @CURSOR INTO @SQL
END
 
CLOSE @CURSOR
DEALLOCATE @CURSOR
GO
 
/* Drop User Defined Functions */
DECLARE @SQL2 NVARCHAR(MAX)
DECLARE @CURSOR2 CURSOR
 
SET @CURSOR2 = CURSOR FAST_FORWARD FOR
    SELECT
        DISTINCT sql = N'DROP FUNCTION [' +
                SPECIFIC_SCHEMA + N'].[' + 
                SPECIFIC_NAME + N'];'
    FROM
        INFORMATION_SCHEMA.ROUTINES
    WHERE
        ROUTINE_TYPE = 'FUNCTION'
 
OPEN @CURSOR2 FETCH NEXT FROM @CURSOR2 INTO @SQL2
 
WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC SP_EXECUTESQL @SQL2
    FETCH NEXT FROM @CURSOR2 INTO @SQL2
END
 
CLOSE @CURSOR2
DEALLOCATE @CURSOR2
GO
 
/* Finally Drop ALL Tables */
EXEC sp_MSforeachtable 'DROP TABLE ?'
GO

Reference


Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.