最近做项目需要查看数据用户表的大小，包括记录条数和占用的磁盘空间数目。在网上找了很久其中 相对还可以。
不过它的2、3中方法返回的数据比较多，有些是我们不关心的数据，我在AdventureWorks2012数据上做的测试。其中第二种方法代码如下：
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.nameOPEN Info_cursorFETCH NEXT FROM Info_cursor INTO @tablename WHILE @@FETCH_STATUS = 0BEGINif 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 = @tablenameFETCH NEXT FROM Info_cursor INTO @tablename ENDCLOSE Info_cursorDEALLOCATE Info_cursorGO--itlearner注：显示数据库信息sp_spaceused @updateusage = 'TRUE' --itlearner注：显示表信息select * from tablespaceinfo order by cast(left(ltrim(rtrim(reserved)) , len(ltrim(rtrim(reserved)))-2) as int) desc
运行效果如图：
很显然这个返回结果是错误的。但是它提供了一种思路，修改后的SQL语句如下：
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 CURSORFOR SELECT '[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' AS Table_Name FROM [INFORMATION_SCHEMA].[TABLES] WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME <> 'tablespaceinfo'OPEN Info_cursorFETCH NEXT FROM Info_cursorINTO @tablenameWHILE @@FETCH_STATUS = 0 BEGIN SET @cmdsql = 'insert into #temp exec sp_spaceused ''' + @tablename + '''' EXECUTE sp_executesql @cmdsql FETCH NEXT FROM Info_cursorINTO @tablename ENDCLOSE Info_cursorDEALLOCATE Info_cursorGO--itlearner注：显示数据库信息--sp_spaceused @updateusage = 'TRUE'--itlearner注：显示表信息UPDATE #tempSET reserved = REPLACE(reserved, 'KB', '') , datainfo = REPLACE(datainfo, 'KB', '') , index_size = REPLACE(index_size, 'KB', '') , unused = REPLACE(unused, 'KB', '')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 #tempDROP TABLE #tempSELECT 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 unusedFROM dbo.tablespaceinfoORDER BY reserved DESC
运行结果如图：
同时他的第三种方法返回的数据太多，很多是我们不怎么关心的，原SQL语句如下：
View Code SELECT OBJECT_NAME(id) tablename , 8 * reserved / 1024 reserved , RTRIM(8 * dpages / 1024) + 'Mb' used , 8 * ( reserved - dpages ) / 1024 unused , 8 * dpages / 1024 - rows / 1024 * minlen / 1024 free , rowsFROM sysindexesWHERE indid = 1ORDER 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_CountFROM sys.sysindexesWHERE indid = 1 AND status = 2066 -- status='18'ORDER BY reserved DESC
运行结果如下：
有不对的地方欢迎大家拍砖！
看了的回复很好，于是把他的回复贴在下面：
exec sp_MSForEachTable@precommand=N' create table ##(id int identity,表名 sysname,字段数 int,记录数 int,保留空间 Nvarchar(10),使用空间 varchar(10),索引使用空间 varchar(10),未用空间 varchar(10))',@command1=N'insert ##(表名,记录数,保留空间,使用空间,索引使用空间,未用空间) exec sp_spaceused ''?'' update ## set 字段数=(select count(*) from syscolumns where id=object_id(''?'')) where id=scope_identity()',@postcommand=N'select * from ## order by convert(INT,replace(保留空间,"KB","")) desc drop table ##'