dreamhua 2019-10-28
declare @x varchar(2000) declare @y varchar(2000) declare @str varchar(max) declare @sql varchar(max) declare @flag int --标记循环次数 set @flag=1 set @str=‘‘--保存建立视图的语句 DECLARE @table_name varchar(2000) DECLARE cursor2 CURSOR FOR --定义游标cursor2 SELECT name FROM (select name from sysobjects where xtype=‘U‘ and name not like ‘%copy%‘ and name not like ‘[A-Z]%‘ and name not like ‘[a-z]%‘ ) t -- 共84条记录 OPEN cursor2 --打开游标 FETCH NEXT FROM cursor2 INTO @table_name WHILE @@FETCH_STATUS=0 --判断是否成功获取数据 BEGIN set @sql=‘select * from syscolumns where id=object_id(‘‘‘+ @table_name +‘‘‘) and name=‘‘is_delete ‘‘‘--单引号需要转义,用两个单引号表示 set @+1 exec(@sql) if @@rowcount=1 --@@rowcount返回受上一语句影响的行数,判断是否有1行受影响 begin if @flag<82 --多个条件需要加() begin set @x=‘ select name=‘‘‘_name+‘‘‘, zero=(SELECT count(*) from ‘+ @table_name +‘ where is_delete=0),one=(SELECT count(*) from ‘+ @table_name +‘ where is_delete=1),two=(SELECT count(*) from ‘+ @table_name +‘ where is_delete=2),three=(SELECT count(*) from ‘+ @table_name +‘ where is_delete=3) union ‘ set @+@x print @flag print @str end else begin print @flag set @y=‘select name=‘‘‘_name+‘‘‘, zero= (SELECT count(*) from ‘+ @table_name +‘ where is_delete=0),one=(SELECT count(*) from ‘+ @table_name +‘ where is_delete=1),two=(SELECT count(*) from ‘+ @table_name +‘ where is_delete=2),three=(SELECT count(*) from ‘+ @table_name +‘ where is_delete=3) ‘ set @str=‘create view tb as ‘+@y print @str exec (@str) break end end FETCH NEXT FROM cursor2 INTO @table_name END CLOSE cursor2 --关闭游标 DEALLOCATE cursor2