求SQL语句:求数据库内各表大小,并排序列出.

如题.
我之前模仿[sp_spaceused]写过一段,但用了一段时间后觉得不准确,请前辈们帮帮忙.

32 个解决方案

#1

我原有的代码
--求表之大小,排序列出

select OBJECT_NAME(ID)
      ,SIZE = sum(reserved) * CONVERT(FLOAT, (SELECT LOW FROM MASTER.DBO.SPT_VALUES WHERE NUMBER = 1 AND TYPE = 'E')) /1024.00/1024.00
      --表大小（字节）= 8192 x Num_Pages (M)
  from sysindexes
  where indid in (0,1,255)
  GROUP BY ID
  ORDER BY SIZE DESC

#2

需要占用空间还是记录数？

占用空间与表所建立的索引有关，比较复杂

#3

你的只计算了数据占用，没有计算索引占用

#4

需要占用空间还是记录数？

占用空间与表所建立的索引有关，比较复杂 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
占用空间!

#5

你的只计算了数据占用，没有计算索引占用 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

是的,这段代码不准确.

#6

数据占用不是库的全部阿，你要是想计算全部的，还要加上其它的占用空间的咚咚
比如说过程,job，索引等等

#7

记号...
sp_spaceused 这个准确不？

#8

数据占用不是库的全部阿，你要是想计算全部的，还要加上其它的占用空间的咚咚
比如说过程,job，索引等等
-------------------------------------------------------------------------
能一一分类列出吗？

#9

sp_spaceused   这个准确不？
---------------------------

哈,这个还不敢质疑. 

#10

貌似LZ在学校Sql2005

我用Sql2005仿照 sp_spaceused 写了一个类似的功能，LZ看看给点意见啊

CREATE PROCEDURE [dbo].[GetTableSpace]
@dbName     sysname = '',       --数据库名，默认当前数据库
@tableName  sysname = '',       --表名，默认全部表
@columnName varchar(50) = '',   --列名，排序用
@sort       varchar(4) = ''     --asc升序，desc降序
AS
IF (@dbName = '') SET @dbName = DB_Name()
ELSE IF (CHARINDEX('M0A2_DB2',@dbName) > 0) SET @dbName = 'M0A2_DB2.' + @dbName
IF (@tableName = '') SET @tableName = '%'
IF (@columnName = '') SET @columnName = 'name'
IF (@sort = '') SET @sort = 'asc'

EXEC ('SELECT  a.name,rows as int,(reserved * 8) reserved,(data * 8)data,
((CASE WHEN used > data THEN (used - data) ELSE 0 END) * 8)index_size,
((CASE WHEN reserved > used THEN (reserved - used) ELSE 0 END) * 8)unused 
 FROM ' + @dbName + '.sys.tables a INNER JOIN 
(SELECT object_id,SUM (reserved_page_count)reserved,SUM (used_page_count)used,
SUM (CASE
  WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
  ELSE lob_used_page_count + row_overflow_used_page_count END)data,
SUM (CASE WHEN (index_id < 2) THEN row_count ELSE 0	END)rows
FROM ' + @dbName + '.sys.dm_db_partition_stats GROUP BY object_id)b
ON a.object_id = b.object_id WHERE a.name LIKE ''' + @tableName + ''' ORDER BY ' + @columnName + ' ' + @sort)
GO

--数据库内各表大小,并排序列出
exec GetTableSpace '','','data','desc'

#11

谢谢高升,我想贴上去试验一下,
可怎没断行的,该怎样复制?

#12

虽然结果出来了, 还得消化呢. 明天再回.

#13

关于 sp_spaceused ....
在SQL 2005里面（好像2000里面也是类似），数据库引擎不会随时对数据库的使用情况做更新，这是由于性能方面的考虑。
如果刚做了大量的数据操作，使用 sp_spaceused的结果是不准确的。

用 DBCC UPDATEUSAGE 可以更新为最准确的数据

实际上 sp_spaceused 有个参数可以指定使用 dbcc updateusage

sp_spaceused [[ @objname = ] 'objname' ] 
[,[ @updateusage = ] 'updateusage' ]

#14

EXEC ('SELECT  a.name,rows as int,(reserved * 8) reserved,(data * 8)data,
((CASE WHEN used > data THEN (used - data) ELSE 0 END) * 8)index_size,
((CASE WHEN reserved > used THEN (reserved - used) ELSE 0 END) * 8)unused 
 FROM ' + @dbName + '.sys.tables a INNER JOIN 
(SELECT object_id,SUM (reserved_page_count)reserved,SUM (used_page_count)used,
SUM (CASE
  WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
  ELSE lob_used_page_count + row_overflow_used_page_count END)data,
SUM (CASE WHEN (index_id < 2) THEN row_count ELSE 0    END)rows
FROM ' + @dbName + '.sys.dm_db_partition_stats GROUP BY object_id)b
ON a.object_id = b.object_id WHERE a.name LIKE ''' + @tableName + ''' ORDER BY ' + @columnName + ' ' + @sort)
GO

谢谢高大哥!
可,我没能把这段东西啃下来. 断句.断句....差点要晕倒了.
请原谅本人才疏学浅,能提高可读性吗？

#15

    *   hb_gx
    * 高升
    * 等 级：


发表于：2007-11-30 16:40:37 10 楼  得分:
貌似LZ在学校Sql2005

我用Sql2005仿照   sp_spaceused   写了一个类似的功能，LZ看看给点意见啊 
________________________________________________________________________

高升,真诚地感谢你,请恕我愚笨,能把思路说得详尽一点吗？ 

另外,如果这份代码需要用在SQL2000上,那又应该怎么写呢?

#16

联机帮助上就有对于表空间大小的计算方法，居然没人去看。

#17

蹭分

#18

上面的代码完全是仿照sp_spaceused写的，主要是我需要知道每天每个数据库的增长情况，因为不想每天手工去纪录，所以写了个存储过程每天跑，因此里面反而写复杂了，不好意思！


Sql2005中有一个动态管理视图sys.dm_db_partition_stats
里面有个字段是reserved_page_count 表示总页数(数据页+索引页+空白页)

我们知道数据库中是以页为单位存储数据，1页是8K，那么 总页数*8 就是你表实际所占用的空间。

只要理解了这个就够了，没必要看懂我上面的代码，毕竟我写Sql语句的水平很有限，反而让人看的不之所云。


看看联机帮助上的 sys.dm_db_partition_stats 能更出错一点



上面代码的返回的结果集 第一个是表的总行数 
reserved 就是LZ需要的表的实际大小，单位是KB
data       是数据页的大小
index_size 是索引页的大小
unused     是空白页的大小

#19

看看联机帮助上的   sys.dm_db_partition_stats   能更明白一点


昨天没休息好，今天老打错字，不好意思了

#20

谢谢高升,你真是能人!佩服你!
那段代码,可能需要一段时间才能消化下来.谢谢你的回复,你所提到的联机帮助我真的得好好啄磨.


Money go my home:
你批评得不错.但不能说大家没有看联机帮助,只能说是还没看得透.
看得出,你一定是理解透了,能来一段像高升那样的代码,让繁复的工作变得轻松又简单吗?
要知道光批评是没有用的.

#21

Mark!

#22

先單獨DBCC UPDATEUSAGE
然后用sp_spaceused 應該就是準確的了

#23

DBCC   UPDATEUSAGE  这个知道的

1楼那段代码所出现的问题是DBCC   UPDATEUSAGE 解决不了的.
一时间忘了当时试验的情形,因为有好几个月了.
让我再想想.

#24

继续......

问一个问题, 表 spt_values 中的信息, 有谁能帮忙解释一下?

spt_values table

field:
name
number
type
low
high
status

#25

这些天总算可以静下心来,
按着高升大哥的指引,
接受SQLServer_2008兄弟的批评,把一些有关的帮助重新理解,
回过头来再看高大哥的那段代码,
发现并不难理解.

试着按自己的理解将上面的代码修改成这个样子,各位高人请多指点哦.

用于SQL2005的:

SELECT object_id
      ,reserved=SUM (reserved_page_count)
      ,used=SUM (used_page_count)
      ,data=SUM(CASE WHEN (index_id < 2) THEN (in_row_data_page_count 
                                             + lob_used_page_count 
                                             + row_overflow_used_page_count)
                     ELSE lob_used_page_count + row_overflow_used_page_count 
                 END)
      ,rows=SUM(CASE WHEN (index_id < 2) THEN row_count 
                     ELSE 0    
                 END)
 into #temp
  FROM sys.dm_db_partition_stats 
   GROUP BY object_id



SELECT  name=object_name(object_id)
       ,rows 
       ,reserved=(reserved * 8)/1024
       ,data=(data * 8)/1024
       ,index_size=((CASE WHEN used > data THEN (used - data) 
                         ELSE 0 
                    END) * 8)/1024
       ,unused=((CASE WHEN reserved > used THEN (reserved - used) 
                      ELSE 0 
                 END) * 8)/1024
  from #temp
     order by reserved desc

drop table #temp

#26

再重提二楼那段用在SQL2000的代码,
这段代码使用了一段时间后,发现有问题.
问题是,发现每次数据收缩前后,执行这段代码所得的结果会有很大差异.
由于当时比较忙,没有时间去根究为什么,一丢下就是好几个月了.
我今天试着在SQL2000, SQL2005 中分别重新做当时的操作,
即先执行二楼的代码,记下各表的大小,
然后做数据收缩,再执行二楼的代码,
再将两次结果比较,
但却没能将问题重现.

#27

此贴延后再结,看能不能把之前的问题找出来.

#28

新年快到,活儿多起来了,要不结帐就变隔年债啰~~
还是先结帐吧,日后若能把问题还原再说. 

谢谢所有给我帮助的朋友!

谢谢高升!谢谢你指引我学习2005的动态视图,并让我产生学习动态SQL语句的兴趣，我阅读过你BLOG上的故事，非常感人，相信有志者事竟成。

还有一个要特别感谢的人是 Limpire(昨夜小楼),谢谢多次帮忙解决难题。

#29

LZ自己写的看起来清楚多了，真了不起

#30

--这个是我在 sql server2000 存储过程与xml编程 看到的视图.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER view [dbo].[vSpaceUsed]
as
select  distinct TOP 100 PERCENT
 	  db_name()			as TABLE_CATALOG
	, user_name(obj.uid)	as TABLE_SCHEMA
	, obj.name				as TABLE_NAME
	, case obj.xtype
		when 'U' then 'BASE TABLE'
		when 'V' then 'VIEW'
	end					as TABLE_TYPE
	, obj.ID				as TABLE_ID
	, Coalesce((select sum(reserved) 
				from sysindexes i1
				where i1.id = obj.id
				and i1.indid in (0, 1, 255)) 
			*	(select d.low from master.dbo.spt_values d 
				where d.number = 1 and d.type = 'E')
		, 0)			as RESERVED
	, Coalesce((select Sum (reserved) - sum(used) 
				from sysindexes i2
				where i2.indid in (0, 1, 255)
				and id = obj.id)  
			* (select d.low from master.dbo.spt_values d 
				where d.number = 1	and d.type = 'E')
		, 0)			as UNUSED
	, case obj.xtype
		when 'U' then Coalesce((select i3.rows
								from sysindexes i3
								where i3.indid < 2
								and i3.id = obj.id), 0)
		when 'V' then NULL
	   end				as [ROWS]
, 	Coalesce
	(	(	(select sum(dpages)	from sysindexes
				where indid < 2 and id = obj.id
			) + (select isnull(sum(used), 0) from sysindexes 
				where indid = 255 and id = obj.id
				)
		) * (select d.low from master.dbo.spt_values d 
				where d.number = 1 and d.type = 'E'
			), 0)		as [DATA]
	, Coalesce(
		((select sum(reserved) 
		from sysindexes i1
		where i1.id = obj.id
		and i1.indid in (0, 1, 255)
		) - ( (select sum(dpages) from sysindexes
				where indid < 2 and id = obj.id
			  ) + (select isnull(sum(used), 0) from sysindexes
					where indid = 255 and id = obj.id)
			) )
	* (select d.low from master.dbo.spt_values d 
		where d.number = 1 and d.type = 'E')
		, 0) 			as [INDEX]	 	
from sysobjects obj
where obj.xtype in ('U', 'V') 
and	permissions(obj.id) != 0
order by db_name(), user_name(obj.uid), obj.name
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

#31

脸红~

觉得自己还要继续努力学习才跟得上,
日后还请各位多指点,多拍砖,少盖高帽.

#32

谢谢蛮MM~~,学习......