Drop all tables within your database

First, let me say that you need to be sure you want to do this.  I've been working SQL Server for longer than I care to admit, and I know, there will come a time when you'll need to do this.

Here is the quick and dirty, using the undocumented sp_MSForEachTable:

  USE YourDatabase

  EXEC sp_MSForEachTable @command1 = "DROP TABLE ?" 

 

As I said, that is an undocumented procedure, and it really is best to avoid using those procedures in production.  Here is another method to do the same:

  --DROP ALL TABLES 
  USE YourDatabaseName
  GO
  
    DECLARE @TableName VARCHAR(150)
    DECLARE @SQL VARCHAR(300)

    SELECT @TableName = (
        SELECT TOP 1 [name] 
        FROM sys.objects 
        WHERE [type] = 'U' 
        ORDER BY [name] )

    WHILE @TableName IS NOT NULL
    BEGIN
        SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@TableName) +']'
        EXEC (@SQL)
        PRINT 'Dropped Table : ' + @TableName
        SELECT @TableName = (
          SELECT TOP 1 [name] 
          FROM sys.objects 
          WHERE [type] = 'U' 
          AND [name] > @TableName 
          ORDER BY [name] )
    END