T-SQL: Drop All Objects in a SQL Server Database

Sometimes you want to clear out a database, but you can't just drop the entire database and create a new one. Recently, a colleague needed to do just that, but couldn't because the database was hosted elsewhere. Here is a simple script that works to drop all objects in any database I've tested it against so far. If your database contains object types that mine don't, you might need to add to this script using the same patterns established here.

declare @n char(1)
set @n = char(10)

declare @stmt nvarchar(max)

-- procedures select @stmt = isnull( @stmt + @n, '' ) +
    'drop procedure [' + name + ']' from sys.procedures

-- check constraints select @stmt = isnull( @stmt + @n, '' ) +
    'alter table [' + object_name( parent_object_id ) + '] drop constraint [' + name + ']' from sys.check_constraints

-- functions select @stmt = isnull( @stmt + @n, '' ) +
    'drop function [' + name + ']' from sys.objects
where type in ( 'FN', 'IF', 'TF' )

-- views select @stmt = isnull( @stmt + @n, '' ) +
    'drop view [' + name + ']' from sys.views

-- foreign keys select @stmt = isnull( @stmt + @n, '' ) +
    'alter table [' + object_name( parent_object_id ) + '] drop constraint [' + name + ']' from sys.foreign_keys

-- tables select @stmt = isnull( @stmt + @n, '' ) +
    'drop table [' + name + ']' from sys.tables

-- user defined types select @stmt = isnull( @stmt + @n, '' ) +
    'drop type [' + name + ']' from sys.types
where is_user_defined = 1

exec sp_executesql @stmt
comments powered by Disqus