Drop All the Tables, Stored Procedures, Triggers, Constraints and All the Dependencies in One SQL Statement

Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement

this script cleans all views, SPS, functions PKs, FKs and tables.

/* Drop all non-system stored procs */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

WHILE @name is not null
BEGIN
    SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Procedure: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all views */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped View: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all functions */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Function: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)

WHILE @name is not null
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint IS NOT NULL
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
        EXEC (@SQL)
        PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

WHILE @name IS NOT NULL
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint is not null
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
        EXEC (@SQL)
        PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Table: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

How to drop all tables from a database with one SQL query?

Use the INFORMATION_SCHEMA.TABLES view to get the list of tables. Generate Drop scripts in the select statement and drop it using Dynamic SQL:

DECLARE @sql NVARCHAR(max)=''

SELECT @sql += ' Drop table ' + QUOTENAME(TABLE_SCHEMA) + '.'+ QUOTENAME(TABLE_NAME) + '; '
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_TYPE = 'BASE TABLE'

Exec Sp_executesql @sql

Sys.Tables Version

DECLARE @sql NVARCHAR(max)=''

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

Note: If you have any foreign Keys defined between tables then first run the below query to disable all foreign keys present in your database.

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

For more information, check here.

Dropping a table with all its dependencies (Microsoft SQL Server)

The best thing to do it is "Generate scripts for Drop"

Select Database -> Right Click -> Tasks -> Generate Scripts - will open wizard for generating scripts

• Select the database -> next
• Set option 'Script to create' to true (want to create)
• Set option 'Script to Drop' to true (want to drop)
• Set option 'Generate script for dependent object' to true -> Next
• Select the Check box to select objects wish to create script
• Select the choice to write script (File, New window, Clipboard)

Execute the script

This way we can customize our script i.e., we can do scripting for selected objects of a database.

I hope this will help you!

Best Wishes, JP

Create loop to drop tables with specific names/criteria

I would use "while" to iterate through the script texts extracted by the identity column in the prepared script table.

Declare @RowNumber As Int = 1
Declare @Cnt As Int
Declare @schema_name sysname = ''
Declare @QueryText nVarChar(max)

Create Table #Script (ID Int Identity(1,1), QueryText nVarChar(max))

Insert Into #Script (QueryText)
Select Concat(N'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''',
                          Quotename(@schema_name), N'.',Quotename([name]),N''') AND type in (N''U'')) DROP TABLE ',
                          Quotename(@schema_name), N'.',Quotename([name]))
From sys.tables 
Where [name] Like 'TABLE_____NAME'

Select @Cnt = Count(*) From #Script

While (@RowNumber<=@Cnt)
    Begin
        Select @QueryText = QueryText From #Script Where ID=@RowNumber

        Exec sp_executesql @QueryText

        RAISERROR ('%s', 0, 1, @QueryText) WITH NOWAIT

        Select @RowNumber=@RowNumber+1
    End

or you can use string_agg to get a script to delete all tables and run it in one go without using a loop.

Declare @QueryText nVarChar(max)
Declare @max nVarChar(max) = ''
Declare @schema_name sysname = ''

Select @QueryText=String_Agg(Concat(@max, N'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''',
                                           Quotename(@schema_name), N'.',Quotename([name]),N''') AND type in (N''U'')) DROP TABLE ',
                                           Quotename(@schema_name), N'.',Quotename([name])), ' ')
From sys.tables 
Where [name] Like 'TABLE_____NAME'

Execute sp_executesql @QueryText

How to remove all prints in stored procedures / triggers?

You can do that by joining the system catalog views sys.sql_modules and sys.objects as

SELECT SCHEMA_NAME(O.schema_id) [Schema], 
       O.Name ObjectName,
       O.Type ObjectType
FROM sys.sql_modules M
INNER JOIN sys.objects O
ON O.object_id = M.object_id
WHERE M.definition LIKE '%PRINT%'

See how it's working on this Live Demo

And if you want to return the definition, you can just add definition column in your SELECT statement as

SELECT SCHEMA_NAME(O.schema_id) [Schema], 
       O.Name ObjectName,
       O.Type ObjectType,
       M.definition
FROM sys.sql_modules M
INNER JOIN sys.objects O
ON O.object_id = M.object_id
WHERE M.definition LIKE '%PRINT%'

Constraint is not enough?

A sample DB

create table Person(
    id int primary key,
    cityId int,
    constraint UK1 unique(cityId, id)
);


create table City (
    id int primary key,
    managerId int,
    constraint FK1 foreign key(id, managerId) references Person(cityId, id)
);

Business process is first assign a person to a City then make him/her a manager of the City.

db<>fiddle including test data

---

Related Topics