查询sqlserver数据库及各表格空间利用情况


1、exec sp_spaceused 查询数据库空间利用情况

 

 

2、exec sp_spaceused 'byscurrent'   //查询表格的空间利用情况

 

 

篇主要实现查看sql server中每个表占用的空间大小以及数据库的使用情况。在学习游标的也可以看看，里边用到了游标。

实现思路为：

1.找到库中所有的表

2.遍历表名，如果是用户表的话。执行存储过程 sp_spaceused 结果如下：

3.执行存储过程sp_executesql，将每个表的信息插入结果存储表 tablespaceinfo.

4.4.执行查询，得到我们想要的结果。

整个程序为：

--判断是否存在结果存储表[tablespaceinfo]。如果不存在，则创建

if not exists
(
   select * from dbo.sysobjects where id = object_id(N'[dbo].[tablespaceinfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
)
create table tablespaceinfo
(
   nameinfo varchar(50),      --表的名字
   rowsinfo int ,             --表的行数
   reserved varchar(20) ,     --表保留的行数
   datainfo varchar(20) ,     --数据量大小
   index_size varchar(20) ,   --索引大小
   unused varchar(20)         --没有被占用的空间
)
 
delete from tablespaceinfo --清空原有数据表
 
declare @tablename varchar(255) --表名称　变量
 
 
 
--申明游标　取出库中所有表名
 
DECLARE Info_cursor CURSOR FOR 
 
select o.name from dbo.sysobjects as o where OBJECTPROPERTY(o.id, N'IsTable') = 1 and o.name not like N'#%%' order by o.name
 
 
OPEN Info_cursor
FETCH NEXT FROM Info_cursor
INTO @tablename
 
WHILE @@FETCH_STATUS = 0
BEGIN
--如果是用户表
if exists 
(
   select * from dbo.sysobjects where id = object_id(@tablename) and OBJECTPROPERTY(id, N'IsUserTable') = 1
)
 
--说明：sp_executesql 执行可以多次重复使用或动态生成的Transact-SQL 语句或批处理
 
-- sp_spaceused 显示行数、保留的磁盘空间以及当前数据库中的表、索引视图
 
-- 或Service Broker 队列所使用的磁盘空间，或显示由整个数据库保留和使用的磁盘空间。
 
--execute sp_executesql N'insert into tablespaceinfo exec sp_spaceused @tbname',N'@tbname varchar(255)',@tbname = @tablename
 
insert into tablespaceinfo exec sp_spaceused @tablename

-- 两种方法是一样的，理论上前者更快

FETCH NEXT FROM Info_cursor
INTO @tablename
END
 
CLOSE Info_cursor --闭关游标
 
DEALLOCATE Info_cursor   --释放游标
 
GO
 
--显示数据库信息
sp_spaceused @updateusage = 'TRUE'
 
--显示表信息
select * from tablespaceinfo order by cast(left(ltrim(rtrim(reserved)) , len(ltrim(rtrim(reserved)))-2) as int) desc

 

--N的作用就是如果你的操作系统是简体中文，你在N后面的单引号里输入了日本、德文、法文什么的，能保证你的字符串是正确的，这时N就要必写；

--反之如果N后面的单引号里只有英文和简体中文(长度不能超过十几K)，那这个N写与不写是一样的。