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.
GO
DECLARE @tbl_name as varchar(256)
DECLARE @flag as int
DECLARE @name as varchar(256)
set @flag=(SELECT count(*) FROM sysobjects where type='U')
while(@flag>0)
BEGIN
set @name=(SELECT top 1 name FROM  sysobjects where type='U')
set @tbl_name = 'drop table ' + @name
exec(@tbl_name)
print @tbl_name
set @flag=@flag-1
end

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.

Regards,

Anil Maharjan