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.
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.
- 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.
- 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.
- 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.
- 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.
- 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.
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.
/* 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
- Read more about dropping tables at StackOverflow.