1: --delete our tables first
2: declare @table_count int;
3: declare @iter int;
4: -- we know we're going to have tables the first time through
5: set @table_count = 1 ;
6: set @iter = 0;
7: -- check to see how many tables we have in the database
8: while @table_count > 0
9: begin
10: exec sp_MSforeachtable "DROP TABLE ? PRINT '? dropped' ";
11: SET @table_count = (select COUNT(*) from sysobjects where type = 'U'and name <> 'dtproperties');
12: SET @iter = @iter + 1;
13: end
14: print 'iterations: ' + CAST(@iter as varchar);
15:
16: -- variable to object name
17: declare @name varchar(100)
18: -- variable to hold object type
19: declare @xtype char(1)
20: -- variable to hold sql string
21: declare @sqlstring nvarchar(1000)
22:
23: declare SPViews_cursor cursor for
24: SELECT sysobjects.name, sysobjects.xtype
25: FROM sysobjects
26: join sysusers on sysobjects.uid = sysusers.uid
27: where OBJECTPROPERTY(sysobjects.id, N'IsProcedure') = 1
28: or OBJECTPROPERTY(sysobjects.id, N'IsView') = 1 and sysusers.name =
29: 'USERNAME'
30:
31: open SPViews_cursor
32:
33: fetch next from SPViews_cursor into @name, @xtype
34:
35: while @@fetch_status = 0
36: begin
37: -- test object type if it is a stored procedure
38: if @xtype = 'P'
39: begin
40: set @sqlstring = 'drop procedure ' + @name
41: exec sp_executesql @sqlstring
42: set @sqlstring = ' '
43: end
44: -- test object type if it is a view
45: if @xtype = 'V'
46: begin
47: set @sqlstring = 'drop view ' + @name
48: exec sp_executesql @sqlstring
49: set @sqlstring = ' '
50: end
51:
52: -- get next record
53: fetch next from SPViews_cursor into @name, @xtype
54: end
55:
56: close SPViews_cursor
57: deallocate SPViews_cursor