Tag Archives: mssql

SQL script for dropping all user created objects (MSSQL, Oracle)

26 Aug

Below is an SQL script to remove a range of user created objects in the current database (tables, views, procedures etc…). Has been tested to work
in MSSQL. Will probably work in Oracle as well but not tested. Use at your own risk.


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

declare @stmt nvarchar(max)

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

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

-- functions
select @stmt = ''
select @stmt = isnull( @stmt + @n, '' ) +
'drop function [' + object_schema_name( object_id ) + '].[' + name + ']'
from sys.objects
where type in ( 'FN', 'IF', 'TF' ) and object_schema_name( object_id ) <> 'ExtProps'
--select @stmt
exec sp_executesql @stmt

-- views
select @stmt = ''
select @stmt = isnull( @stmt + @n, '' ) +
'drop view [' + object_schema_name( object_id ) + '].[' + name + ']'
from sys.views where object_schema_name( object_id ) <> 'ExtProps'
--select @stmt
exec sp_executesql @stmt

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

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

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

-- Schemas
select @stmt = ''
select @stmt = isnull( @stmt + @n, '' ) +
'drop schema [' + name + ']'
from sys.schemas
where schema_id between 5 and 1000
--select @stmt
exec sp_executesql @stmt