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

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