SQL SERVER 游标删除无数据试图
Declare @SQLToken varchar(max)
set @SQLToken = ''
declare hCForEachTable cursor global for
select '[' + REPLACE(schema_name(syso.schema_id), N']', N']]') + ']' + '.' + '[' + REPLACE(object_name(o.id), N']', N']]') + ']'
from dbo.sysobjects o
set @SQLToken = ''
declare hCForEachTable cursor global for
select '[' + REPLACE(schema_name(syso.schema_id), N']', N']]') + ']' + '.' + '[' + REPLACE(object_name(o.id), N']', N']]') + ']'
from dbo.sysobjects o
join sys.all_objects syso
on o.id = syso.object_id
on o.id = syso.object_id
where OBJECTPROPERTY(o.id, N'IsVIEW') = 1 and o.category & 2 = 0
OPEN hCForEachTable;
Declare @tablename varchar(max)
Declare @Temp varchar(max)
FETCH NEXT FROM hCForEachTable;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM hCForEachTable into @tablename;
set @Temp = 'if not exists(select 1 from '+@tablename+') drop view '+@tablename + ';'
set @SQLToken= @SQLToken + @Temp
set @Temp = ''
set @tablename = ''
END;
SELECT @SQLToken
CLOSE hCForEachTable;
DEALLOCATE hCForEachTable;
OPEN hCForEachTable;
Declare @tablename varchar(max)
Declare @Temp varchar(max)
FETCH NEXT FROM hCForEachTable;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM hCForEachTable into @tablename;
set @Temp = 'if not exists(select 1 from '+@tablename+') drop view '+@tablename + ';'
set @SQLToken= @SQLToken + @Temp
set @Temp = ''
set @tablename = ''
END;
SELECT @SQLToken
CLOSE hCForEachTable;
DEALLOCATE hCForEachTable;
作者:DataStrategy
出处:https://www.cnblogs.com/xiongnanbin/
联系:1183744742@qq.com;xiongnanbin@126.com
本文版权归作者和博客园共有(转载的归原作者所有),欢迎转载,但是请在文章页面明显位置给出原文连接。如有问题或建议,请多多留言、赐教,非常感谢。