如何判斷一個資料庫裡面各個 table 和 index 佔用的空間

最新推荐文章于 2025-05-01 14:37:02 发布

njmike_cn

最新推荐文章于 2025-05-01 14:37:02 发布

阅读量722

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL SERVER 文章标签： table join null object insert go

本文链接：https://blog.youkuaiyun.com/njmike_cn/article/details/1623940

SQL SERVER 专栏收录该内容

6 篇文章

订阅专栏

本文介绍了一个SQL Server存储过程sp_SYS_spaceused的创建及使用方法。该过程用于查询数据库中各类对象（如用户表或系统表）的空间使用情况，并可以根据不同参数进行排序输出。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_SYS_spaceused]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[sp_SYS_spaceused]

if exists (select 1 from sysobjects where name = 'sp_SYS_spaceused' and type = 'P')

drop procedure sp_SYS_spaceused

create procedure sp_SYS_spaceused(

@type varchar(2) = 'U', -- The objects type we want size on.

@order varchar(15) = 'name',

@updateusage varchar(5) = false -- Param. for specifying that

)

create table #spt_space

(

id int not null primary key,

name sysname,

rows int null,

reserved float null,

data float null,

indexp float null,

unused float null

)

/*-------------------------------------------------------

Check the object type.

-------------------------------------------------------*/

if @type not in ('U','S') -- no physical data storage.

begin

raiserror(55000,-1,-1,@type)

return (1)

end

/*-------------------------------------------------------

Check to see if user wants usages updated.

-------------------------------------------------------*/

if @updateusage is not null

begin

select @updateusage=lower(@updateusage)

if @updateusage not in ('true','false')

begin

raiserror(15143,-1,-1,@updateusage)

return(1)

end

if @updateusage = 'true'

begin

dbcc updateusage(0) with no_infomsgs

print ' '

end

set nocount on

--id, name, rows

insert into #spt_space (id, name, rows, data)

select i.id, o.name, i.rows, 0

from sysindexes i inner join sysobjects o on i.id = o.id

where i.indid < 2 and o.type = @type

--reserved

update #spt_space

set reserved = r.reserved

from ( select o.id, sum(i.reserved) as reserved from sysindexes i inner join sysobjects o on i.id = o.id

where i.indid in (0, 1, 255) and o.type = @type group by o.id) r

where #spt_space.id = r.id

--data

update #spt_space

set data = data + r.pages

from ( select o.id, isnull(sum(i.dpages),0) as pages from sysindexes i inner join sysobjects o on i.id = o.id

where i.indid <2 and o.type = @type group by o.id) r

where #spt_space.id = r.id

update #spt_space

set data = data + r.used

from ( select o.id, isnull(sum(used), 0) as used from sysindexes i inner join sysobjects o on i.id = o.id

where i.indid = 255 and o.type = @type group by o.id) r

where #spt_space.id = r.id

--index page

update #spt_space

set indexp = r.used - data

from ( select o.id, sum(convert(dec(15),used)) as used from sysindexes i inner join sysobjects o on i.id = o.id

where i.indid in (0, 1, 255) and o.type = @type group by o.id) r

where #spt_space.id = r.id

--unused page

update #spt_space

set unused = reserved - r.used

from ( select o.id, sum(convert(dec(15),used)) as used from sysindexes i inner join sysobjects o on i.id = o.id

where i.indid in (0, 1, 255) and o.type = @type group by o.id) r

where #spt_space.id = r.id

--update spt_sapce

update #spt_space

set reserved= (reserved * d.low / 1024),

data = (data * d.low / 1024 ),

indexp = (indexp * d.low / 1024),

unused = (unused * d.low / 1024)

from master.dbo.spt_values d

where d.number = 1 and d.type = 'E'

--output

if @order = 'name'

select rtn_name = #spt_space.name,

rtn_rows = convert(char(11), rows),

rtn_reserved = ltrim(str(reserved ,15,0) +' ' + 'KB'),

rtn_data = ltrim(str(data ,15,0) +' ' + 'KB'),

rtn_index_size = ltrim(str(indexp ,15,0) + ' ' + 'KB'),

rtn_unused = ltrim(str(unused ,15,0) + ' ' + 'KB')

from #spt_space

order by #spt_space.name

else if @order = 'rows'

select rtn_name = #spt_space.name,

rtn_rows = convert(char(11), rows),

rtn_reserved = ltrim(str(reserved ,15,0) +' ' + 'KB'),

rtn_data = ltrim(str(data ,15,0) +' ' + 'KB'),

rtn_index_size = ltrim(str(indexp ,15,0) + ' ' + 'KB'),

rtn_unused = ltrim(str(unused ,15,0) + ' ' + 'KB')

from #spt_space

order by #spt_space.rows

else if @order = 'reserved'

select rtn_name = #spt_space.name,

rtn_rows = convert(char(11), rows),

rtn_reserved = ltrim(str(reserved ,15,0) +' ' + 'KB'),

rtn_data = ltrim(str(data ,15,0) +' ' + 'KB'),

rtn_index_size = ltrim(str(indexp ,15,0) + ' ' + 'KB'),

rtn_unused = ltrim(str(unused ,15,0) + ' ' + 'KB')

from #spt_space

order by #spt_space.reserved

else if @order = 'data'

select rtn_name = #spt_space.name,

rtn_rows = convert(char(11), rows),

rtn_reserved = ltrim(str(reserved ,15,0) +' ' + 'KB'),

rtn_data = ltrim(str(data ,15,0) +' ' + 'KB'),

rtn_index_size = ltrim(str(indexp ,15,0) + ' ' + 'KB'),

rtn_unused = ltrim(str(unused ,15,0) + ' ' + 'KB')

from #spt_space

order by #spt_space.data

else if @order = 'index_size'

select rtn_name = #spt_space.name,

rtn_rows = convert(char(11), rows),

rtn_reserved = ltrim(str(reserved ,15,0) +' ' + 'KB'),

rtn_data = ltrim(str(data ,15,0) +' ' + 'KB'),

rtn_index_size = ltrim(str(indexp ,15,0) + ' ' + 'KB'),

rtn_unused = ltrim(str(unused ,15,0) + ' ' + 'KB')

from #spt_space

order by #spt_space.indexp

else if @order = 'unused'

select rtn_name = #spt_space.name,

rtn_rows = convert(char(11), rows),

rtn_reserved = ltrim(str(reserved ,15,0) +' ' + 'KB'),

rtn_data = ltrim(str(data ,15,0) +' ' + 'KB'),

rtn_index_size = ltrim(str(indexp ,15,0) + ' ' + 'KB'),

rtn_unused = ltrim(str(unused ,15,0) + ' ' + 'KB')

from #spt_space

order by #spt_space.unused

return 0