【转载】sp_spaceused2,看库里全部表占用的物理空间
摘要:我经常使用sp_Spaceused查看表占用的空间,但一次只能查看一个对象。我今天有空的时候写了一个sp——空间使用2,你可以同时查看所有表的空间大小。
我常用sp_spaceused来查看表所占的空间大小,可是一次只能看一个对象的。
今天有空写了个sp_spaceused2,可以同时看全部表的空间大小。
use master
go
EXEC sp_addmessage @msgnum = 55000, @severity = 16,
@msgtext = N'Objects of type %s do not have space allocated.',
@lang = 'us_english',@replace = 'replace'
![【转载】sp_spaceused2,看库里全部表占用的物理空间第1张](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
EXEC sp_addmessage @msgnum = 55000, @severity = 16,
@msgtext = N'没有为类型 %1! 的对象分配的空间。',
@lang = '简体中文',@replace = 'replace'
go
if exists (select 1 from sysobjects where name = 'sp_spaceused2' and type = 'P')
drop procedure sp_spaceused2
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
)
![【转载】sp_spaceused2,看库里全部表占用的物理空间第1张](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
-- Check the object type.
if @type not in ('U','S') -- no physical data storage.
begin
raiserror(55000,-1,-1,@type)
return (1)
end
![【转载】sp_spaceused2,看库里全部表占用的物理空间第1张](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
-- Check to see if user wants usages updated.
![【转载】sp_spaceused2,看库里全部表占用的物理空间第1张](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
if @updateusage is not null
begin
select @updateusage=lower(@updateusage)
![【转载】sp_spaceused2,看库里全部表占用的物理空间第1张](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
if @updateusage not in ('true','false')
begin
raiserror(15143,-1,-1,@updateusage)
return(1)
end
end
![【转载】sp_spaceused2,看库里全部表占用的物理空间第1张](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
if @updateusage = 'true'
begin
dbcc updateusage(0) with no_infomsgs
print ' '
end
![【转载】sp_spaceused2,看库里全部表占用的物理空间第1张](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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
![【转载】sp_spaceused2,看库里全部表占用的物理空间第1张](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--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
![【转载】sp_spaceused2,看库里全部表占用的物理空间第1张](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--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
![【转载】sp_spaceused2,看库里全部表占用的物理空间第1张](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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
![【转载】sp_spaceused2,看库里全部表占用的物理空间第1张](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![【转载】sp_spaceused2,看库里全部表占用的物理空间第1张](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--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
![【转载】sp_spaceused2,看库里全部表占用的物理空间第1张](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--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
![【转载】sp_spaceused2,看库里全部表占用的物理空间第1张](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--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'
![【转载】sp_spaceused2,看库里全部表占用的物理空间第1张](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return 0
go
![【转载】sp_spaceused2,看库里全部表占用的物理空间第1张](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![【转载】sp_spaceused2,看库里全部表占用的物理空间第1张](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
sp_MS_marksystemobject 'sp_spaceused2'
![【转载】sp_spaceused2,看库里全部表占用的物理空间第1张](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
go
use [dbname]
exec sp_spaceused2
![【转载】sp_spaceused2,看库里全部表占用的物理空间第1张](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
出处:http://blog.myspace.cn/1300316663/archive/2007/05/23/249582259.aspx免责声明:文章转载自《【转载】sp_spaceused2,看库里全部表占用的物理空间》仅用于学习参考。如对内容有疑问,请及时联系本站处理。
上篇Qt中如何禁掉所有UI操作以及注意事项(转)安装gnvm (windows下nodejs版本管理工具)下篇
宿迁高防,2C2G15M,22元/月;香港BGP,2C5G5M,25元/月 雨云优惠码:MjYwNzM=