在工作中,你可能也曾被同样的问题所困扰,即如何确定一个数据库所有数据表的大小。很遗憾的是,在SQL中,并没有一个简单而直观的方法来查看数据库中所有表的大小,但是系统提供了一个有用的存储过程sp_spaceused ,只要我们充分利用、稍加变通,即可得到我们所需要的信息。
sp_spaceused可用来查询行数、保留的磁盘空间以及当前数据库中的表所使用的磁盘空间,或显示由整个数据库保留和使用的磁盘空间。
执行存储过程(exec sp_spaceused 数据表名),即可得到这个表的行数,占用空间大小等信息,但是执行这个过程一次只能查询一个表的信息,如何能一次查找数据库中所有表的信息,这就需要我们另辟蹊径。
在SQL数据库中,有一个存储所有数据表名的系统表sysobjects,根据这个表取出的数据表名,我们就可以利用sp_spaceused通过编写查询语句而一次性得到所有数据表的信息。
一、首先建立一个表用来存储查询数据表的结果:(//后内容为注释)
create TABLE tablespaceinfo( //表名
nameinfo varchar(50), //数据表名
rowsinfo int, //表中现有的行数
reserved varchar(20), //表保留的空间总量
datainfo varchar(20), //表中的数据所使用的空间量
index_size varchar(20), //表中的索引所使用的空间量
unused varchar(20) //表中未用的空间量
)
二、利用游标从系统表中逐个取出表名
DECLARE @tablename varchar(255); //存放数据表名的变量
DECLARE Info_cursor CURSOR FOR //定义游标
SELECT [name] FROM sysobjects WHERE type='U';
//从系统表中取出表类型为用户的表名
OPEN Info_cursor //打开游标
FETCH NEXT FROM Info_cursor INTO @tablename //取数据表名到@tablename
三、根据取出的表名执行存储过程,将查询结果插入到我们在步骤一中所建立的表tablespaceinfo中。
WHILE @@FETCH_STATUS = 0
BEGIN
insert into @tablespaceinfo exec sp_spaceused @tablename
// 将查询结果插入表tablespaceinfo
FETCH NEXT FROM Info_cursor
INTO @tablename
END
四、关闭游标,从表tablespaceinfo查询所有数据表的大小信息
CLOSE Info_cursor
DEALLOCATE Info_cursor
//关闭游标
SELECT * FROM tablespaceinfo ORDER BY rowsinfo DESC
//按行数从大到小排列数据表的大小信息
总结:至此,我们已给出如何利用sp_spaceused获取所有数据表大小信息的完整步骤,在实际操作中,选中所需要查询的数据库,打开查询分析器,将下面的相应的代码拷贝到查询分析器中,执行该代码即可得到数据表大小的信息。现将完整查询语句罗列如下:
create TABLE tablespaceinfo(
nameinfo varchar(50),
rowsinfo int,
reserved varchar(20),
datainfo varchar(20),
index_size varchar(20),
unused varchar(20)
)
DECLARE @tablename varchar(255);
DECLARE Info_cursor CURSOR FOR
SELECT [name] FROM sysobjects WHERE type='U';
OPEN Info_cursor
FETCH NEXT FROM Info_cursor INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
insert into tablespaceinfo exec sp_spaceused @tablename
FETCH NEXT FROM Info_cursor
INTO @tablename
END
CLOSE Info_cursor
DEALLOCATE Info_cursor
SELECT * FROM tablespaceinfo
ORDER BY rowsinfo DESC
sp_spaceused可用来查询行数、保留的磁盘空间以及当前数据库中的表所使用的磁盘空间,或显示由整个数据库保留和使用的磁盘空间。
执行存储过程(exec sp_spaceused 数据表名),即可得到这个表的行数,占用空间大小等信息,但是执行这个过程一次只能查询一个表的信息,如何能一次查找数据库中所有表的信息,这就需要我们另辟蹊径。
在SQL数据库中,有一个存储所有数据表名的系统表sysobjects,根据这个表取出的数据表名,我们就可以利用sp_spaceused通过编写查询语句而一次性得到所有数据表的信息。
一、首先建立一个表用来存储查询数据表的结果:(//后内容为注释)
create TABLE tablespaceinfo( //表名
nameinfo varchar(50), //数据表名
rowsinfo int, //表中现有的行数
reserved varchar(20), //表保留的空间总量
datainfo varchar(20), //表中的数据所使用的空间量
index_size varchar(20), //表中的索引所使用的空间量
unused varchar(20) //表中未用的空间量
)
二、利用游标从系统表中逐个取出表名
DECLARE @tablename varchar(255); //存放数据表名的变量
DECLARE Info_cursor CURSOR FOR //定义游标
SELECT [name] FROM sysobjects WHERE type='U';
//从系统表中取出表类型为用户的表名
OPEN Info_cursor //打开游标
FETCH NEXT FROM Info_cursor INTO @tablename //取数据表名到@tablename
三、根据取出的表名执行存储过程,将查询结果插入到我们在步骤一中所建立的表tablespaceinfo中。
WHILE @@FETCH_STATUS = 0
BEGIN
insert into @tablespaceinfo exec sp_spaceused @tablename
// 将查询结果插入表tablespaceinfo
FETCH NEXT FROM Info_cursor
INTO @tablename
END
四、关闭游标,从表tablespaceinfo查询所有数据表的大小信息
CLOSE Info_cursor
DEALLOCATE Info_cursor
//关闭游标
SELECT * FROM tablespaceinfo ORDER BY rowsinfo DESC
//按行数从大到小排列数据表的大小信息
总结:至此,我们已给出如何利用sp_spaceused获取所有数据表大小信息的完整步骤,在实际操作中,选中所需要查询的数据库,打开查询分析器,将下面的相应的代码拷贝到查询分析器中,执行该代码即可得到数据表大小的信息。现将完整查询语句罗列如下:
create TABLE tablespaceinfo(
nameinfo varchar(50),
rowsinfo int,
reserved varchar(20),
datainfo varchar(20),
index_size varchar(20),
unused varchar(20)
)
DECLARE @tablename varchar(255);
DECLARE Info_cursor CURSOR FOR
SELECT [name] FROM sysobjects WHERE type='U';
OPEN Info_cursor
FETCH NEXT FROM Info_cursor INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
insert into tablespaceinfo exec sp_spaceused @tablename
FETCH NEXT FROM Info_cursor
INTO @tablename
END
CLOSE Info_cursor
DEALLOCATE Info_cursor
SELECT * FROM tablespaceinfo
ORDER BY rowsinfo DESC