Drop all Tables, Stored Procedures, Views and Triggers

Dropping all Tables, Stored Procedures, Views and Triggers: An Overview

Sometimes, we need to remove all tables, stored procedures, views, and triggers from the database. If you have around 100 tables, stored procedures, and views in your database, removing them completely from the database becomes a tedious task. In this SQL Server tutorial, I would like to share the script by which you can remove them completely from the database.

Read More: SQL Server Interview Questions and Answers

Before moving forward, you need to be thorough with tables, stored procedures, views, and triggers. If not, refer to these:

• Basics of SQL Commands
• Different Types of Stored Procedure in SQL Server
• Different Types of Views in SQL Server
• Different Types of Triggers In SQL Server
• Cursors In SQL Server

Importance of Dropping Tables, Stored Procedures, Views, and Triggers

There are several reasons for this. Some of them are:

• Resource Management: It helps free up valuable system resources, such as disk space and memory, improving overall database performance and scalability.
• Data Integrity: It reduces the risk of data inconsistencies and integrity violations by eliminating redundant objects that could interfere with data operations.
• Security: Unnecessary objects can increase the vulnerability for unauthorized access or exploitation if not properly secured or maintained.
• Maintenance: It simplifies database administration and reduces complexity.
• Backup and Restore Efficiency: Dropping unused database objects can reduce the size of database backups and improve backup and restore efficiency.

Read More - DBMS Interview Questions For Freshers

Prerequisites for Dropping all Tables, Stored Procedures, Views, and Triggers in SQL Server

• Backup: Take a full database backup before dropping any objects to recover in case of data loss or corruption.
• Review Dependencies: Identify and review dependencies between tables, stored procedures, views, and triggers.
• Check Permissions: Check that you have the necessary permissions to drop objects in the database.
• Dependency Scripts: Generate dependency scripts or use database documentation tools to document the dependencies between objects.
• Review Scripts: Review any scripts or applications that interact with the database.
• Transaction Management: Use transactions to ensure data consistency and roll back changes if necessary.

Permission to Execute the Scripts for dropping all tables, stored procedures, views, and triggers in the SQL Server

1. Tables:
   • ALTER or CONTROL permission on the schema to which the table belongs.
2. Stored Procedures:
   • ALTER or CONTROL permission on the schema to which the stored procedure belongs.
   • ALTER ANY PROCEDURE or CONTROL SERVER permission at the server level to drop any stored procedure in any schema.
3. Views:
   • ALTER or CONTROL permission on the schema to which the view belongs.
   • ALTER ANY VIEW or CONTROL SERVER permission at the server level to drop any view in any schema.
4. Triggers:
   • ALTER or CONTROL permission on the schema to which the trigger belongs.
   • ALTER ANY DATABASE DDL TRIGGER or CONTROL SERVER permission at the server level to drop any trigger in any schema.

Drop all Tables

1. Retrieve the names of all user-defined tables from the sys.tables system catalog view. Construct dynamic SQL statements to generate DROP TABLE statements for each table.
2. Execute the generated DROP TABLE statements using dynamic SQL to drop each table in the database.

   
   DECLARE @tableName NVARCHAR(128)
   
   DECLARE cursorTables CURSOR FOR
   SELECT [name]
   FROM sys.tables
   WHERE is_ms_shipped = 0 -- Select user-defined tables only
   
   OPEN cursorTables
   
   FETCH NEXT FROM cursorTables INTO @tableName
   
   WHILE @@FETCH_STATUS = 0
   BEGIN
       EXEC('DROP TABLE ' + QUOTENAME(@tableName))
       FETCH NEXT FROM cursorTables INTO @tableName
   END
   
   CLOSE cursorTables
   DEALLOCATE cursorTables
    

This script uses a cursor to iterate over all user-defined tables in the database and drops each one individually using dynamic SQL.

Remove all User-defined Stored Procedures

1. Write a script to generate DROP PROCEDURE statements for all user-defined stored procedures in the database.
2. Execute the generated DROP PROCEDURE statements to remove the stored procedures from the database.

    
   -- drop all user defined stored procedures
   Declare @procName varchar(500) 
   Declare cur Cursor For Select [name] From sys.objects where type = 'p' 
   Open cur 
   Fetch Next From cur Into @procName 
   While @@fetch_status = 0 
   Begin 
    Exec('drop procedure ' + @procName) 
    Fetch Next From cur Into @procName 
   End
   Close cur 
   Deallocate cur
    

This script uses a cursor to iterate over all user-defined stored procedures in the database (excluding system procedures) and generates a DROP PROCEDURE statement for each one.

Remove all Views

1. Generate the DROP VIEW statements dynamically using a cursor to iterate over all user-defined views in the database.
2. Execute the generated DROP VIEW statements to remove each view.

    
   -- drop all user defined views
   Declare @viewName varchar(500) 
   Declare cur Cursor For Select [name] From sys.objects where type = 'v' 
   Open cur 
   Fetch Next From cur Into @viewName 
   While @@fetch_status = 0 
   Begin 
    Exec('drop view ' + @viewName) 
    Fetch Next From cur Into @viewName 
   End
   Close cur 
   Deallocate cur 
   

This script creates a cursor to iterate over all user-defined views in the database and generates a DROP VIEW statement for each view using dynamic SQL.

Remove all Triggers

-- drop all user defined triggers
Declare @trgName varchar(500) 
Declare cur Cursor For Select [name] From sys.objects where type = 'tr' 
Open cur 
Fetch Next From cur Into @trgName 
While @@fetch_status = 0 
Begin 
 Exec('drop trigger ' + @trgName) 
 Fetch Next From cur Into @trgName 
End
Close cur 
Deallocate cur 

This script iterates over all user-defined triggers in the database using a cursor, and for each trigger, it dynamically generates and executes a DROP TRIGGER statement to remove it.

What do you think?

I hope you will enjoy the tips while playing with SQL Server. I would like to have feedback from my blog readers. If you want to gain a practical understanding, you can enroll in our SQL Server Course.

Do you Know?

.NET is gaining popularity day by day, especially after the release of .NET 8. .NET 8 is not only a framework version but much more than that. It redefines the way software applications are built and deployed, enabling developers to meet the evolving demands of modern computing.

Therefore, if you want to upskill yourselves and stand out from others consider our following training programs on .NET.

• .NET Developer Training With Certification
• ASP.NET Core Certification Training
• ASP.NET Core Course
• .NET Solution Architect Certification Training
• Full-Stack .NET Developer Certification Training Program
• Advanced Full-Stack .NET Developer Certification Training

Take our Sqlserver skill challenge to evaluate yourself!

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET FREE CHALLENGE