Drop all tables from the Database

If u want to delete all User created tables from database.
This Query will help....

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

Explanation: 

SELECT * FROM sysobjects 

Well, here is all the type definitions you find in sysobjects table :

D - default
F - SQLJ function
L - log
P - Transact-SQL or SQLJ procedure
PR - prepare objects (created by Dynamic SQL)
R - rule
RI - referential constraint
S - system table
TR - trigger
U - user table
V - view
XP - extended stored procedure

Category 2 is system objects and 0 is user created objects...

If u want to clean the database u can use below query on the third line.

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