Drop Every Database Object - SQL Server

When executed against the database of interest, the following T-SQL code will generate a set of SQL statements which, when executed against the same database, will drop every object in the current database.

Notes

This script will NOT delete any schemas
Be sure to CHANGE THE CURRENT DATABASE before running this script!

with ForeignKeys as (
    select
         fkName = '[' + foreignKey.name + ']'
        ,schemaName = s.name
        ,onTable = onTable.name
        ,onTableFullName = '[' + s.name + '].[' + onTable.name + ']'
    from

        sysforeignkeys fk

        inner join sysobjects foreignKey
            on foreignKey.id = fk.constid

        inner join sysobjects onTable
            on fk.fkeyid = onTable.id
    		
        inner join sysusers s
            on onTable.uid = s.uid

    where 1=1
        and onTable.type = 'U'
    --  and onTable.is_ms_shipped = 0
        and onTable.name not like 'sys%'

    )
/*-----------------------------------------------------------------------------------------------*/
,ObjectList as (
    select 
         name = o.name
        ,ObjectFullName = '[' + s.name + '].[' + o.name + ']'
        ,ObjectType = o.type
    from 
        sysobjects o
        inner join sysusers s
            on o.uid = s.uid
    where 1=1
        and type in ('FN','IF','P','TF','U','V')
      --and is_ms_shipped = 0
        and o.name not like 'sys%'
    )
/*-----------------------------------------------------------------------------------------------*/
select
     sequence = 1
    ,name = fkName
    ,sql = 'IF OBJECT_ID(N''' + onTableFullName + ''') IS NOT NULL AND OBJECT_ID(N''' 
        + fkName + ''') IS NOT NULL ALTER TABLE ' + onTableFullName 
        + ' drop constraint ' + fkName 
from
    foreignkeys

union select 
     sequence = 2
    ,name = ObjectFullName
    ,sql = 'IF OBJECT_ID(N''' + ObjectFullName + ''') IS NOT NULL DROP ' + case objectType 
        when 'FN' then 'FUNCTION'
        when 'IF' then 'FUNCTION'
        when 'P'  then 'PROCEDURE'
        when 'TF' then 'FUNCTION'
        when 'U'  then 'TABLE'
        when 'V'  then 'VIEW'
        end + ' ' + ObjectFullName

from 
    ObjectList

union select
     sequence = 3
    ,name = ''
    ,sql = 'select * from sysobjects'

order by 
     sequence
    ,name

Drop Every Database Object - SQL Server

See Also¶