用脚本查看某库中每个表大小

来源：互联网发布：中国货币供给量数据编辑：程序博客网时间：2024/05/22 05:04

/* ==== SQL Server 2000 ==== */

declare @id             int
declare @type           character(2)
declare @pages          int
declare @dbname         sysname
declare @dbsize         dec(15,0)
declare @bytesperpage   dec(15,0)
declare @pagesperMB     dec(15,0)

create table #spt_space
(
     [objid]          int null,
     [rows]           int null,
     [reserved]       dec(15) null,
     [data]           dec(15) null,
     [indexp]         dec(15) null,
     [unused]         dec(15) null
)

set nocount on

-- Create a cursor to loop through the user tables

declare c_tables cursor for
select id from sysobjects where xtype = 'U'

open c_tables fetch next from c_tables into @id

while @@fetch_status = 0
begin
     /* Code from sp_spaceused */
     insert into #spt_space (objid, reserved)
     select objid = @id, sum(reserved)
     from sysindexes
     where indid in (0, 1, 255)    and    id = @id


     select @pages = sum(dpages)
     from sysindexes
     where indid < 2
     and    id = @id

     select @pages = @pages + isnull(sum(used), 0)
     from sysindexes
     where indid = 255     and    id = @id

     update #spt_space    set data = @pages
     where objid = @id

     /* index: sum(used) where indid in (0, 1, 255) - data */

     update #spt_space
     set indexp = (select sum(used)
     from sysindexes
     where indid in (0, 1, 255)
     and id = @id) - data
     where objid = @id

     /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
     update #spt_space
     set unused = reserved - (
                                 select sum(used)
                                 from sysindexes
                                 where indid in (0, 1, 255)   and id = @id
                             )
     where objid = @id

     update #spt_space    set [rows] = i.[rows]
     from sysindexes i
     where i.indid < 2    and i.id = @id     and objid = @id

     fetch next from c_tables    into @id

end

select
TableName = (select left(name,60) from sysobjects where id = objid),
[Rows] = convert(char(11), rows),
[Reserved(MB)] = cast(reserved * d.low / 1024.0 / 1024.0 as numeric(15,3)),
[Data(MB)] = cast(data * d.low / 1024.0 / 1024.0 as numeric(15,3)),
[IndexSize(MB)] = cast(indexp * d.low / 1024.0 / 1024.0 as numeric(15,3)),
[Unused(MB)] = cast(unused * d.low / 1024.0 / 1024.0 as numeric(15,3))
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
order by reserved desc

drop table #spt_space
close c_tables
deallocate c_tables

/*==== SQL Server S2008 ====*/

declare  @spaceused table( name varchar(50), rows int, reserved varchar(20), data varchar(20), index_size varchar(20), unused varchar(20) )
insert into @spaceused exec sp_MSforeachtable "exec sp_spaceused '?'"
select name  as [表名]
, [rows] as [行数]
, cast(cast(replace(reserved,'KB','') as numeric(20,4))/1204/1204  as numeric(20,3)) as [总大小(GB)]
, cast(cast(replace(data,'KB','') as numeric(20,4))/1204/1204  as numeric(20,3)) as [数据(GB)]
, cast(cast(replace(index_size,'KB','') as numeric(20,4))/1204/1204  as numeric(20,3)) as [索引(GB)]
, cast(cast(replace(unused,'KB','') as numeric(20,4))/1204/1204  as numeric(20,3)) as [未用(GB)]
from  @spaceused
order by 3 desc

0 0