unD6jvjy

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