如何使用SQL语句查询数据库及表的空间容量

- 统计某个表的空间大小, 行数信息
EXEC Your_DB.dbo.sp_spaceused 'dbo.t_log';



-- 统计多个数据库多个表的空间大小, 行数信息
create table tmp_table_space(table_name varchar(50), table_rows int, total_size varchar(20),data_size varchar(20), index_size varchar(20),
unused_size varchar(20), dbname varchar(30));
insert into tmp_table_space(table_name,table_rows,total_size,data_size,index_size,unused_size)
EXEC Your_DB.dbo.sp_spaceused 'dbo.t_log';
insert into tmp_table_space(table_name,table_rows,total_size,data_size,index_size,unused_size)
EXEC Your_DB.dbo.sp_spaceused 'dbo.t_log2';
update tmp_table_space set dbname='AHBZMJ' where dbname is null;

/*************************************************************************
用于查看对应数据库的大小、占用空间以及该数据库中各个系统表、用户表
使用方法： 在查询分析器中选择您要查看的数据库，然后运行此代码即可。
**************************************************************************/
----新建一个表spt_result_table存储数据库中各个表的空间信息
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spt_result_table]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[spt_result_table]
GO

create table spt_result_table
(
tablename varchar(776) null, ----表名
rows varchar(776) null, ----表中现有的行数
reserved varchar(776) null, ----为表保留的空间总量
data varchar(776) null, ----表中的数据所使用的空间量
indexp varchar(776) null, ----表中的索引所使用的空间量
unused varchar(776) null ----表中未用的空间量
)ON [PRIMARY]
GO

----创建存储过程prc_database_spaceused：计算数据库大小及各个表占用空间的情况
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[prc_database_spaceused]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[prc_database_spaceused]
GO

create procedure prc_database_spaceused
as

BEGIN
declare @id int
declare @type character(2)
declare @pages int
declare @dbname sysname ----数据库名
declare @dbsize dec(15,0) ----数据库大小
declare @logsize dec(15)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)
declare @objname varchar(776) ----记录表名

declare @database_size varchar(776)
declare @unallocated_space varchar(776)
select @dbname = db_name() ----数据库为当前数据库
create table #spt_space
(
rows int null,
reserved dec(15) null, ----保留的空间总量
data dec(15) null, ----数据使用的空间总量

indexp dec(15) null, ----索引使用的空间
unused dec(15) null ----未用的空间量
)

---- 计算数据大小(以kB页为单位)
select @dbsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 = 0)

---- 计算日志大小(以kB页为单位)
select @logsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 <> 0)

---- 求得一个page有多少bytes
select @bytesperpage = low
from master.dbo.spt_values
where number = 1 and type = 'E'

---- 计算MB占多少page（MB = 1048576B）
select @pagesperMB = 1048576 / @bytesperpage

---- 计算数据库大小
set @database_size = ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + ' MB')

---- 计算未用的空间量的大小
set @unallocated_space = ltrim(str((@dbsize -
(select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
)) / @pagesperMB,15,2)+ ' MB')

---- 保留的空间总量
insert into #spt_space (reserved)
select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)

select @pages = sum(convert(dec(15),dpages))
from sysindexes
where indid < 2

select @pages = @pages + isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255

---- 数据使用的空间总量
update #spt_space
set data = @pages

---- 索引使用的空间
update #spt_space
set indexp = (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))- data

---- 未用的空间量
update #spt_space
set unused = reserved - (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
---- 输出数据库大小信息
select
database_name = @dbname,
database_size = @database_size,
unallocated_space = @unallocated_space,
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'

---- 清空临时表#spt_space
delete from #spt_space

-----定义游标，计算表大小信息
declare cur_table cursor for
select name
from sysobjects
where xtype = 'U' or xtype = 'S'
order by xtype asc

-----打开游标
open cur_table
fetch next from cur_table

into @objname
while (@@fetch_status = 0)
begin

select @id = null
select @id = id,
@type = xtype
from sysobjects
where id = object_id(@objname)

--dbcc updateusage(0,@objname) with no_infomsgs

insert into #spt_space (reserved)
select 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

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

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

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

insert into spt_result_table
select tablename = object_name(@id),
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'

truncate table #spt_space

fetch next from cur_table into @objname
end
close cur_table
deallocate cur_table

select * from spt_result_table where tablename is not null;
drop table #spt_space
END
GO

exec prc_database_spaceused

---- 删除spt_result_table表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spt_result_table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[spt_result_table]
GO

---- 删除存储过程prc_database_spaceused
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[prc_database_spaceused]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[prc_database_spaceused]
GO