Skip to content

Instantly share code, notes, and snippets.

@jdaigle
Created June 14, 2013 12:10
Show Gist options
  • Save jdaigle/5781341 to your computer and use it in GitHub Desktop.
Save jdaigle/5781341 to your computer and use it in GitHub Desktop.
drop all user objects from a database
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 [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' from INFORMATION_SCHEMA.TABLES
-- user defined types
select @stmt = isnull( @stmt + @n, '' ) +
'drop type [' + name + ']' from sys.types
where is_user_defined = 1
exec sp_executesql @stmt
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment