在SQL中查看表所占的空间大小

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

begin

    drop procedure sp_spaceused2

end

go

 

create procedure sp_spaceused2 (

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

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

-- usage info. should be updated.

)

as

 

create table #spt_space

(

    id int not null primary key,

    name sysname,

    rows int null,

    reserved dec(15) null,

    data dec(15) null,

    indexp dec(15) null,

    unused dec(15) 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

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

 

--output

select #spt_space.name,

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

    reserved = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),

    data = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),

    index_size = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),

    unused = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')

from #spt_space, master.dbo.spt_values d

where d.number = 1

    and d.type = 'E'

Order by (reserved * d.low / 1024.) desc,

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

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

        (unused * d.low / 1024.) desc

 

return 0

go

 

--調試

--exec sp_spaceused2

sp_MS_marksystemobject 'sp_spaceused2'

功能

显示当前数据库中某个类型表的行数、由它保留和使用的磁盘空间。

语法

sp_spaceused2 [[@type=] 'type']
[,[@updateusage =] 'updateusage']

参数

[@type =] 'type'

是要显示的表的类型名称。可以是下列对象类型中的一种：
S = 系统表
U = 用户表
type 的数据类型是 varchar(2)，默认设置为 'U'。

[@updateusage =] 'updateusage'

表示应在数据库内运行 DBCC UPDATEUSAGE。值可以是 true 或 false。updateusage 的数据类型是 varchar(5)，默认设置为 FALSE。

返回代码值

0（成功）或 1（失败）

结果集

返回针对指定类型的表的结果集。

列名	数据类型	描述
name	nvarchar(128)	表名。
rows	char(11)	表中现有的行数。
reserved	varchar(18)	为表保留的空间总量。
data	varchar(18)	表中的数据所使用的空间总量。
index_size	varchar(18)	表中的索引所使用的空间总量。
unused	varchar(18)	为表保留但尚未使用的空间总量。

注释

sp_spaceused 计算数据和索引使用的磁盘空间量以及当前数据库中的表所使用的磁盘空间量。

当指定 updateusage 时，Microsoft SQL Server 扫描数据库中的数据页，并就每个表使用的存储空间对 sysindexes 表作出任何必要的纠正。例如会出现这样一些情况：当除去索引后，表的 sysindexes 信息可能不是当前的。该进程在大表或数据库上可能要花一些时间运行。只有当怀疑所返回的值不正确，而且该进程对数据库中的其它用户或进程没有负面影响时，才应使用该进程。如果需要，可以单独运行 DBCC UPDATEUSAGE。