批量删除表，sql server循环查询表数据(循环数据库中的表记录游标循环删除动态表)

广告：

DECLARE @tableName NVARCHAR(255)
DECLARE tableCursor CURSOR FOR
select name from sysobjects where name like 'TMP%'
OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @tableName

WHILE @@FETCH_STATUS = 0
BEGIN 
 --PRINT @tableName
 exec('drop table '+ @TableName)
 FETCH NEXT FROM tableCursor INTO @tableName
END

CLOSE tableCursor
DEALLOCATE tableCursor 

DECLARE @tableName NVARCHAR(255)
DECLARE tableCursor CURSOR FOR
select name from sysobjects where name like 'TMP%' and and OBJECTPROPERTY(id, N'IsUserTable') = 1
OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @tableName

WHILE @@FETCH_STATUS = 0
BEGIN 
 --PRINT @tableName
 exec('dbcc checktable('+@TableName+',repair_allow_data_loss)')
 FETCH NEXT FROM tableCursor INTO @tableName
END

CLOSE tableCursor
DEALLOCATE tableCursor 

DECLARE @tableName NVARCHAR(255)
DECLARE tableCursor CURSOR FOR
select name from sysobjects where name like 'TMP%' and xtype='U'
OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @tableName

WHILE @@FETCH_STATUS = 0
BEGIN 
 --PRINT @tableName
 exec('dbcc checktable('+@TableName+',repair_allow_data_loss)')
 FETCH NEXT FROM tableCursor INTO @tableName
END

CLOSE tableCursor
DEALLOCATE tableCursor 

广告：