Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Drop Every Database Object - SQL Server

RSS
Modified on Tue, Oct 04, 2011, 1:50 PM by Administrator Categorized as 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
  1. This script will NOT delete any schemas
  2. 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

See Also

List Every Database Object

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.