用游标删除所有符合条件的表

use tzyj_motortest_data GO if (exists (select * from sysobjects where name = N'mt_A')) drop table mt_A GO create table mt_A(  aID int identity primary key not null,  bID int not null,  cName varchar(50) not null ) GO if (exists (select * from sysobjects where name = N'mt_B')) drop table mt_B GO create table mt_B(  bID int identity primary key not null,  yyyData varchar(100) ) GO /*  使用游标删除所有符合条件的表 */ declare @name varchar(128) declare @icount int declare curTableName CURSOR for select name from sysobjects where name like 'mt_tbl%' open curTableName fetch next from curTableName into @name while (@@fetch_status = 0) begin set @icount = @icount + 1 exec ('drop table ' + @name) fetch next from curTableName into @name end print @icount close curTableName deallocate curTableName GO declare @tblName varchar(50) declare @icount int declare @sql nvarchar(1000) declare @rowcount int set nocount on set @icount = 0 while (@icount < 50) begin exec mt_pCreateTableNameWithRand @tblName output SET @sql=N'SELECT @A=COUNT(*) FROM sysobjects WHERE [NAME] = ''' + @tblName + '''' EXEC sp_executeSQL @sql, N'@A INT OUTPUT', @rowCount OUTPUT  -- 如果参数为NULL 或者 存在相同表名的表 则重新生成一个表名 IF @tblName IS NULL AND @rowCount <= 0 BEGIN  waitfor delay '00:00:01.000' EXEC mt_pCreateTableNameWithRand @tblName OUTPUT END exec ('create table ' + @tblName + '(dataID int identity primary key not null,  data varchar(100))') insert into mt_A  (bID,cName) values (@icount % 10 + 1, @tblName)  waitfor delay '00:00:00.100' set @icount = @icount + 1 end set nocount off GO select * from mt_A order by bID GO declare @icount int 0079 declare @data varchar(100) set nocount on set @icount = 0 while (@icount < 10) begin set @data = 'ABC' + str(@icount) insert into mt_B  (yyyData) values(@data) set @icount = @icount + 1 end 0090 GO set nocount off select * from mt_B GO if (exists (select * from sysobjects where name = 'mt_pDelXXXTable')) drop procedure mt_pDelXXXTable GO create proc mt_pDelXXXTable (  @bID int ) AS declare @errCde int declare @tblName varchar(128) begin set nocount on declare curTblName CURSOR for select cName from mt_A where bID = @bID open curTblName fetch next from curTblName into @tblName while(@@Fetch_Status = 0) begin exec ('drop table ' + @tblName) fetch next from curTblName into @tblName end close curTblName deallocate curTblName delete from mt_A where bID = @bID delete from mt_B where bID = @bID end GO exec mt_pDelXXXTable 5 select * from mt_A order by bID select * from mt_B