Skip to content

Instantly share code, notes, and snippets.

@mwittmann
Forked from jammycakes/dropall.sql
Last active April 28, 2020 00:57
Show Gist options
  • Save mwittmann/3be760f74f384e5a09e457fe390b722a to your computer and use it in GitHub Desktop.
Save mwittmann/3be760f74f384e5a09e457fe390b722a to your computer and use it in GitHub Desktop.
A script to drop all objects from a SQL Server database
-- Support for multiple schema names; drop all table constraints
--------------------------------------------------
declare @name varchar(100)
declare @table varchar(100)
declare @schema varchar(100)
-- Drop all table constraints: this goes through alter table
declare c cursor for
select a.name as [constraint], b.name as [table], OBJECT_SCHEMA_NAME(b.id) as [schema]
from dbo.sysobjects a
inner join dbo.sysobjects b on a.parent_obj = b.id
where a.xtype in ('F','D','C','UQ') and b.xtype='U'
open c
fetch next from c into @name, @table, @schema
while @@FETCH_STATUS = 0
begin
exec ('alter table [' + @schema + '].[' + @table + '] drop constraint [' + @name + ']')
fetch next from c into @name, @table, @schema
end
close c
deallocate c
GO
if exists (select * from dbo.sysobjects where name='TestFramework_DropAll' and xtype='P')
drop procedure TestFramework_DropAll
GO
create procedure TestFramework_DropAll (@xtype varchar(2), @drop varchar(20))
as
begin
declare @name varchar(100), @id bigint, @schema varchar(100)
declare c cursor for select name, id, OBJECT_SCHEMA_NAME(id) from dbo.sysobjects where xtype=@xtype
open c
fetch next from c into @name, @id, @schema
while @@FETCH_STATUS = 0
begin
if @name != 'TestFramework_DropAll' and @schema != 'sys'
exec ('DROP ' + @drop + ' [' + @schema + '].[' + @name + ']')
fetch next from c into @name, @id, @schema
end
close c
deallocate c
end
GO
-- Drop stuff in this order to avoid dependency errors
exec TestFramework_DropAll 'V', 'view'
GO
exec TestFramework_DropAll 'FN', 'function'
GO
exec TestFramework_DropAll 'IF', 'function'
GO
exec TestFramework_DropAll 'TF', 'function'
GO
exec TestFramework_DropAll 'U', 'table'
GO
exec TestFramework_DropAll 'P', 'procedure'
GO
-- User defined types are a special case as they are not listed in sysobjects
declare c cursor for
select name from sys.types where is_user_defined=1
declare @name varchar(100)
open c
fetch next from c into @name
while @@FETCH_STATUS = 0
begin
exec ('drop type [' + @name + ']')
fetch next from c into @name
end
close c
deallocate c
GO
-- Drop schemas except for standard/built-in ones
declare c cursor for
select name from sys.schemas where name not in ('dbo','guest','INFORMATION_SCHEMA','sys')
declare @name varchar(100)
open c
fetch next from c into @name
while @@FETCH_STATUS = 0
begin
exec ('drop schema [' + @name + ']')
fetch next from c into @name
end
close c
deallocate c
GO
exec TestFramework_DropAll 'D', 'default'
GO
drop procedure TestFramework_DropAll
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment