关于查看MSSQL 数据库 用户每个表 占用的空间大小

最近做项目需要查看数据用户表的大小,包括记录条数和占用的磁盘空间数目。在网上找了很久其中查看MSsql数据库每个表占用的空间大小 相对还可以。
不过它的2、3中方法返回的数据比较多,有些是我们不关心的数据,我在AdventureWorks2012数据上做的测试。其中第二种方法代码如下:
<div class="codetitle"><a style="CURSOR: pointer" data="19224" class="copybut" id="copybut19224" onclick="doCopy('code19224')"> 代码如下:

<div class="codebody" id="code19224">
View Code
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 @cmdsql varchar(500)
DECLARE Info_cursor CURSOR FOR
select o.name
from dbo.sysobjects 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)
execute sp_executesql
N'insert into tablespaceinfo exec sp_spaceused @tbname',
N'@tbname varchar(255)',
@tbname = @tablename
FETCH NEXT FROM Info_cursor
INTO @tablename
END
CLOSE Info_cursor
DEALLOCATE Info_cursor
GO
--itlearner注:显示数据库信息
sp_spaceused @updateusage = 'TRUE'
--itlearner注:显示表信息
select
from tablespaceinfo
order by cast(left(ltrim(rtrim(reserved)),len(ltrim(rtrim(reserved)))-2) as int) desc

运行效果如图:

<IMG alt="" src="https://files.jb51.cc/file_images/article/201306/2013062110341526.jpg"&gt;

很显然这个返回结果是错误的。但是它提供了一种思路,<SPAN style="COLOR: #ff0000">修改后的sql语句如下:
<div class="codetitle"><a style="CURSOR: pointer" data="10938" class="copybut" id="copybut10938" onclick="doCopy('code10938')"> 代码如下:
<div class="codebody" id="code10938">
View Code
IF NOT EXISTS ( SELECT

FROM sys.tables
WHERE name = 'tablespaceinfo' )
BEGIN
CREATE TABLE tablespaceinfo --创建结果存储表
(
Table_Name VARCHAR(50),
Rows_Count INT,
reserved INT,
datainfo INT,
index_size INT,
unused INT
)
END
DELETE FROM tablespaceinfo
--清空数据表
CREATE TABLE #temp --创建结果存储表
(
nameinfo VARCHAR(50),
rowsinfo INT,
reserved VARCHAR(20),
datainfo VARCHAR(20),
index_size VARCHAR(20),
unused VARCHAR(20)
)
DECLARE @tablename VARCHAR(255)
--表名称
DECLARE @cmdsql NVARCHAR(500)
DECLARE Info_cursor CURSOR
FOR
SELECT '[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' AS Table_Name
FROM [INFORMATION_SCHEMA].[TABLES]
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME <> 'tablespaceinfo'
OPEN Info_cursor
FETCH NEXT FROM Info_cursor
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmdsql = 'insert into #temp exec sp_spaceused ''' + @tablename
+ ''''
EXECUTE sp_executesql @cmdsql
FETCH NEXT FROM Info_cursor
INTO @tablename
END
CLOSE Info_cursor
DEALLOCATE Info_cursor
GO
--itlearner注:显示数据库信息
--sp_spaceused @updateusage = 'TRUE'
--itlearner注:显示表信息
UPDATE #temp
SET reserved = REPLACE(reserved,'KB',''),
datainfo = REPLACE(datainfo,
index_size = REPLACE(index_size,
unused = REPLACE(unused,'')
INSERT INTO dbo.tablespaceinfo
SELECT nameinfo,
CAST(rowsinfo AS INT),
CAST(reserved AS INT),
CAST(datainfo AS INT),
CAST(index_size AS INT),
CAST(unused AS INT)
FROM #temp
DROP TABLE #temp
SELECT Table_Name,
Rows_Count,
CASE WHEN reserved > 1024
THEN CAST(reserved / 1024 AS VARCHAR(10)) + 'Mb'
ELSE CAST(reserved AS VARCHAR(10)) + 'KB'
END AS Data_And_Index_Reserved,
CASE WHEN datainfo > 1024
THEN CAST(datainfo / 1024 AS VARCHAR(10)) + 'Mb'
ELSE CAST(datainfo AS VARCHAR(10)) + 'KB'
END AS Used,
CASE WHEN Index_size > 1024
THEN CAST(index_size / 1024 AS VARCHAR(10)) + 'Mb'
ELSE CAST(index_size AS VARCHAR(10)) + 'KB'
END AS index_size,
CASE WHEN unused > 1024 THEN CAST(unused / 1024 AS VARCHAR(10)) + 'Mb'
ELSE CAST(unused AS VARCHAR(10)) + 'KB'
END AS unused
FROM dbo.tablespaceinfo
ORDER BY reserved DESC

运行结果如图:

<IMG alt="" src="https://files.jb51.cc/file_images/article/201306/2013062110341527.jpg"&gt;

同时他的第三种方法返回的数据太多,很多是我们不怎么关心的,原sql语句如下:
<div class="codetitle"><a style="CURSOR: pointer" data="88911" class="copybut" id="copybut88911" onclick="doCopy('code88911')"> 代码如下:
<div class="codebody" id="code88911">
View Code

SELECT OBJECT_NAME(id) tablename,
* reserved / 1024 reserved,
RTRIM(8 * dpages / 1024) + 'Mb' used,
* ( reserved - dpages ) / 1024 unused,
* dpages / 1024 - rows / 1024 * minlen / 1024 free,
rows
FROM sysindexes
WHERE indid = 1
ORDER BY reserved DESC

运行结果如图:

这里面包含一些索引信息,其实我们只关心表占用磁盘信息,修改后的sql语句如下:
代码如下:
View Code
SELECT OBJECT_NAME(id) tablename,
CASE WHEN reserved * 8 > 1024 THEN RTRIM(8 * reserved / 1024) + 'MB'
ELSE RTRIM(reserved * 8) + 'KB'
END DataReserve,
CASE WHEN dpages * 8 > 1024 THEN RTRIM(8 * dpages / 1024) + 'MB'
ELSE RTRIM(dpages * 8) + 'KB'
END Used,
CASE WHEN 8 * ( reserved - dpages ) > 1024
THEN RTRIM(8 * ( reserved - dpages ) / 1024) + 'MB'
ELSE RTRIM(8 * ( reserved - dpages )) + 'KB'
END unused,
CASE WHEN ( 8 * dpages / 1024 - rows / 1024 * minlen / 1024 ) > 1024
THEN RTRIM(( 8 * dpages / 1024 - rows / 1024 * minlen / 1024 )
/ 1024) + 'MB'
ELSE RTRIM(( 8 * dpages / 1024 - rows / 1024 * minlen / 1024 ))
+ 'KB'
END FREE,
rows AS Rows_Count
FROM sys.sysindexes
WHERE indid = 1
AND status = 2066 -- status='18'
ORDER BY reserved DESC

运行结果如下:

有不对的地方欢迎大家拍砖!

数据库数据库空间大小

相关文章

Hessian开源的远程通讯,采用二进制 RPC的协议,基于 HTTP 传输。可以实现PHP调用Java,Python,C#等多语...
初识Mongodb的一些总结,在Mac Os X下真实搭建mongodb环境,以及分享个Mongodb管理工具,学习期间一些总结...
边看边操作,这样才能记得牢,实践是检验真理的唯一标准.光看不练假把式,光练不看傻把式,边看边练真把式....
在php中,结果输出一共有两种方式:echo和print,下面将对两种方式做一个比较。 echo与print的区别: (...
在安装好wampServer后,一直没有使用phpMyAdmin,今天用了一下,phpMyAdmin显示错误:The mbstring exte...
变量是用于存储数据的容器,与代数相似,可以给变量赋予某个确定的值(例如:$x=3)或者是赋予其它的变...