sqlserver游标体验
发布时间：2020-12-12 15:54:40 所属栏目：MsSql教程 来源：网络整理
导读：if exists (select * from dbo.sysobjects where id = object_id('[dbo].[TableSpace]') and OBJECTPROPERTY(id,'IsUserTable') = 1) drop table [dbo].[TableSpace] go create table TableSpace ( ?TableName? varchar(20),?RowsCount char(11),?Reserved v
|
if exists (select * from dbo.sysobjects where id = object_id('[dbo].[TableSpace]') and OBJECTPROPERTY(id,'IsUserTable') = 1) drop table [dbo].[TableSpace] go create table TableSpace ( ?TableName? varchar(20),?RowsCount char(11),?Reserved varchar(18),?Data? varchar(18),?Index_size varchar(18),?Unused? varchar(18)? ) go declare @sql varchar(500) declare @TableName varchar(20) declare Cursor1 Cursor for ?select name from sysobjects where xtype='U' open Cursor1 fetch next from Cursor1 into @TableName while @@fetch_status = 0 begin ?set @sql = 'insert into TableSpace ' ?set @sql = @sql + ' exec sp_spaceused '''+ @TableName + ''' ' ?exec (@sql) ?fetch next from Cursor1 into @TableName end close Cursor1 deallocate Cursor1 go --显示结果 select * from TableSpace --order by TableName --order by TableName ASC?? --按表名称，用于统计表 --order by RowsCount DESC?? --按行数量，用于查看表行数 --order by Reserved DESC,Data DESC? --按占用空间 --order by Index_size DESC,Reserved DESC? --按索引空间查看 go --查看库的使用状况,可以随时执行的。 --exec sp_spaceused jobs --go select * into? #TTA? from TableSpace declare @num char(11) declare cursor2 cursor for select RowsCount from #TTA where RowsCount > 6 open cursor2 fetch next from cursor2 into @num while @@fetch_status = 0 begin ??? print @num ??? fetch next from cursor2 into @num end close cursor2 deallocate cursor2 go
（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |