1、流控制
--一直到11:07:00才运行
--waitfor time '11:07:00'
declare @temp table(schematable varchar(100))
declare @tablename varchar(100)
insert into @temp
(schematable)
select s.name + '.' + t.name
from sys.tables t
inner join sys.schemas s
on t.schema_id = s.schema_id
where t.type_desc = 'User_table'
--while循环结构
while (select COUNT(*) from @temp ) > 0
begin
select top (1) @tablename = schematable
from @temp
exec sp_spaceused @tablename; --输出当前数据库中每个表的空间使用情况
delete from @temp
where schematable = @tablename
--if-else分支结构
if( (select COUNT(*) from @temp)=0 )
begin
--延迟10秒
waitfor delay '00:00:10'
exec sp_spaceused; --输出当前数据库的空间使用情况
break;
end
else
begin
continue;
end
end
2、游标
set nocount on
declare @session_id smallint
--1.定义游标
declare session_cur cursor
local --作用范围:局部游标(LOCAL,GLOBAL)
forward_only --滚动方式:向前(FORWARD_ONLY,SCROLL)
fast_forward --获取数据方式:快速向前(STATIC,KEYSET,DYNAMIC,FAST_FORWARD)
read_only --只读 (READ_ONLY,SCROLL_LOCKS,OPTIMISTIC)
for select session_id
from sys.dm_exec_requests
where status in ('runnable','sleeping','running')
and session_id >50
--2.打开游标
open session_cur
--3.取下一条
fetch next from session_cur
into @session_id
--4.开始循环
while @@FETCH_STATUS = 0
begin
print 'spid #:' + str(@session_id);
exec('dbcc outputbuffer(' + @session_id + ')') ;
fetch next from session_cur
into @session_id
end
close session_cur --5.关闭游标
deallocate session_cur --6.释放游标占用的资源