本篇文章是对查看MSSQL数据库用户每个表占用的空间大小进行了详细的分析介绍，需要的朋友参考下
最近做项目需要查看数据用户表的大小，包括记录条数和占用的磁盘空间数目。在网上找了很久其中查看MSSQL数据库每个表占用的空间大小 相对还可以。
不过它的2、3中方法返回的数据比较多，有些是我们不关心的数据，我在AdventureWorks2012数据上做的测试。其中第二种方法代码如下：
复制代码 代码如下:
View Code
if not exists select * from dbo.sysobjects where id = object_idN'[dbo].[tablespaceinfo]’) and OBJECTPROPERTYid, N’IsUserTable’) = 1)
create table tablespaceinfo –创建结果存储表
nameinfo varchar50) ,
rowsinfo int , reserved varchar20) ,
datainfo varchar20) ,
index_size varchar20) ,
unused varchar20) )
delete from tablespaceinfo –清空数据表
declare @tablename varchar255) –表名称
declare @cmdsql varchar500)
DECLARE Info_cursor CURSOR FOR
select o.name
from dbo.sysobjects o where OBJECTPROPERTYo.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 OBJECTPROPERTYid, N’IsUserTable’) = 1)
execute sp_executesql
N’insert into tablespaceinfo exec sp_spaceused @tbname’,
N’@tbname varchar255)’,
@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 castleftltrimrtrimreserved)) , lenltrimrtrimreserved)))-2) as int) desc
运行效果如图：
很显然这个返回结果是错误的。但是它提供了一种思路，修改后的SQL语句如下：
复制代码 代码如下:
View Code
IF NOT EXISTS SELECT *
FROM sys.tables
WHERE name = ‘tablespaceinfo’ )
BEGIN
CREATE TABLE tablespaceinfo –创建结果存储表
Table_Name VARCHAR50) ,
Rows_Count INT ,
reserved INT ,
datainfo INT ,
index_size INT ,
unused INT
)
END
DELETE FROM tablespaceinfo
–清空数据表
CREATE TABLE #temp –创建结果存储表
nameinfo VARCHAR50) ,
rowsinfo INT ,
reserved VARCHAR20) ,
datainfo VARCHAR20) ,
index_size VARCHAR20) ,
unused VARCHAR20)
)
DECLARE @tablename VARCHAR255)
–表名称
DECLARE @cmdsql NVARCHAR500)
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 = REPLACEreserved, ‘KB’, ”) ,
datainfo = REPLACEdatainfo, ‘KB’, ”) ,
index_size = REPLACEindex_size, ‘KB’, ”) ,
unused = REPLACEunused, ‘KB’, ”)
INSERT INTO dbo.tablespaceinfo
SELECT nameinfo ,
CASTrowsinfo AS INT) ,
CASTreserved AS INT) ,
CASTdatainfo AS INT) ,
CASTindex_size AS INT) ,
CASTunused AS INT)
FROM #temp
DROP TABLE #temp
SELECT Table_Name ,
Rows_Count ,
CASE WHEN reserved > 1024
THEN CASTreserved / 1024 AS VARCHAR10)) + ‘Mb’
ELSE CASTreserved AS VARCHAR10)) + ‘KB’
END AS Data_And_Index_Reserved ,
CASE WHEN datainfo > 1024
THEN CASTdatainfo / 1024 AS VARCHAR10)) + ‘Mb’
ELSE CASTdatainfo AS VARCHAR10)) + ‘KB’
END AS Used ,
CASE WHEN Index_size > 1024
THEN CASTindex_size / 1024 AS VARCHAR10)) + ‘Mb’
ELSE CASTindex_size AS VARCHAR10)) + ‘KB’
END AS index_size ,
CASE WHEN unused > 1024 THEN CASTunused / 1024 AS VARCHAR10)) + ‘Mb’
ELSE CASTunused AS VARCHAR10)) + ‘KB’
END AS unused
FROM dbo.tablespaceinfo
ORDER BY reserved DESC
运行结果如图：
同时他的第三种方法返回的数据太多，很多是我们不怎么关心的，香港服务器，原SQL语句如下：
复制代码 代码如下:
View Code
SELECT OBJECT_NAMEid) tablename ,
* reserved / 1024 reserved ,
RTRIM8 * 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语句如下：
复制代码 代码如下: