How to drop/delete all the user define tables within a SQL database.

Hello all,

Firstly on this lovely Valentine’s day esp, I wish Happy Valentine’s day to all the loved ones 🙂 .

I would like to post some another blog which might be helpful to the one who works in SQL.

Sometimes we might need to delete all the user define tables from a Database in SQL. So, If any one just need to delete all the user define tables from a SQL DB then you can use just a simple Query as below:

USE Database:--replace with your Database name.
DECLARE @tbl_name as varchar(256)
DECLARE @flag as int
DECLARE @name as varchar(256)
set @flag=(SELECT count(*) FROM sysobjects where type='U')
set @name=(SELECT top 1 name FROM  sysobjects where type='U')
set @tbl_name = 'drop table ' + @name
print @tbl_name
set @flag=@flag-1

within a database we can have objects as TABLES, VIEWS, FUNCTIONS, PROCEDURES, TRIGGERS, etc. are there. So, If anyone just want to delete only User Define TABLES without dropping the entire Database and other objects the above query is the best one. If someone drops the database that means those objects needs to be recreated. So,in order to save the time and for quick approach I found this useful .

This is just a simple example and looks simple but it might help for the one who indeed needs such a solution.


Anil Maharjan

Leave a Reply