Financial Year 2023 End Sale: Get upto 40% OFF on Job-oriented Training! Offer Ending in
D
H
M
S
Get Now
Browse Tutorials
Drop all Tables, Stored Procedures, Views and Triggers

Drop all Tables, Stored Procedures, Views and Triggers

16 Mar 2024
Intermediate
201K Views
11 min read

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:

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.

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.

FAQs

Q1. How do I drop all tables and views in SQL Server?

To drop all tables and views in SQL Server, use dynamic SQL combined with the DROP TABLE and DROP VIEW statements.

Q2. How to delete all tables and stored procedures from database in SQL Server?

  1. To delete all tables: EXEC sp_MSforeachtable 'DROP TABLE ?'
  2. To delete all stored procedures: EXEC sp_MSforeachdb 'USE ?; EXEC sp_MSforeachprocedure ''DROP PROCEDURE ?'' '

Q3. Can we DROP TABLE from the stored procedure?

Yes, you can use the DROP TABLE statement within a stored procedure to drop a table.

Q4. How do I delete all triggers in a table in SQL Server?

To delete all triggers associated with a specific table in SQL Server, you can query the sys.triggers system catalog view to identify the triggers and then drop them individually.

Take our free sqlserver skill challenge to evaluate your skill

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

GET CHALLENGE

Share Article
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Accept cookies & close this