1hH9Uk2X

1/*===================================================================
2
3	Esborra tot el que hi hagi dins d'una db
4
5===================================================================*/
6declare @name varchar(100)
7declare @table varchar(100)
8
9-- Drop all foreign key constraints: this goes through alter table
10
11declare c cursor for
12	select a.name as [constraint], b.name as [table] from dbo.sysobjects a
13		inner join dbo.sysobjects b on a.parent_obj = b.id
14		where a.xtype='F' and b.xtype='U'
15open c
16fetch next from c into @name, @table
17while @@FETCH_STATUS = 0
18begin
19	exec ('alter table [' + @table + '] drop constraint [' + @name + ']')
20	fetch next from c into @name, @table
21end
22close c
23deallocate c
24
25go
26
27if exists (select * from dbo.sysobjects where name='TestFramework_DropAll' and xtype='P')
28	drop procedure TestFramework_DropAll
29
30go
31
32create procedure TestFramework_DropAll (@xtype varchar(2), @drop varchar(20))
33as
34begin
35	declare @name varchar(100)
36	declare c cursor for select name from sysobjects where xtype=@xtype
37	open c
38	fetch next from c into @name
39	while @@FETCH_STATUS = 0
40	begin
41		if @name != 'TestFramework_DropAll'
42			exec ('DROP ' + @drop + ' [' + @name + ']')
43		fetch next from c into @name
44	end
45	close c
46	deallocate c
47end
48
49go
50
51-- Drop stuff in this order to avoid dependency errors
52
53exec TestFramework_DropAll 'V', 'view'
54go
55exec TestFramework_DropAll 'FN', 'function'
56go
57exec TestFramework_DropAll 'IF', 'function'
58go
59exec TestFramework_DropAll 'TF', 'function'
60go
61exec TestFramework_DropAll 'U', 'table'
62go
63exec TestFramework_DropAll 'P', 'procedure'
64go
65
66
67-- User defined types are a special case as they are not listed in sysobjects
68
69declare c cursor for
70	select name from sys.types where is_user_defined=1
71declare @name varchar(100)
72open c
73fetch next from c into @name
74while @@FETCH_STATUS = 0
75begin
76	exec ('drop type [' + @name + ']')
77	fetch next from c into @name
78end
79close c
80deallocate c
81
82go
83
84exec TestFramework_DropAll 'D', 'default'
85go
86
87
88drop procedure TestFramework_DropAll
89
90go