批量刪除表，sql server循環查詢表數據(循環數據庫中的表記錄游標循環刪除動態表)
發布時間：2024/8/3 8:51:52 作者：Admin 閱讀：21
廣告：
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
廣告：
相關文章
