Useful StorePrecedure
2006-04-01 23:07:35 来源:WEB开发网sp_disk
列出sql server(WINDOWS平台上强大的数据库平台)中所有数据库设备的逻辑名,物理设备名,设备号,设备大小。
use master
go
create proc sp_disk
as
set nocount on
select
logical_name=substring(name,1,12),
physical_name=substring(phyname,1,25),
vdevno=low/power(2,24),
size=(high-low+1)/512
from
master.dbo.sysdevices
where
status & 2=2
order by
vdevno
go
grant execute on sp_disk to public
go
sp_freedisk
列出sql server(WINDOWS平台上强大的数据库平台)中所有设备的逻辑名,物理设备名,设备号,设备大小,已经使用的设备空间,剩余设备空间。
use master
go
create proc sp_freedisk
as
set nocount on
select
logical_name = substring(d.name,1,12),
physical_name = substring(d.phyname,1,17),
vdevno = d.low/power(2,24),
size = (d.high- d.low+1)/512,
reserved = isnull(sum(u.size)/512,0),
left = (d.high-d.low+1)/512 - isnull(sum(u.size)/512,0)
from
master.dbo.sysdevices d, master.dbo.sysusages u
where
d.status & 2 =2
and
u.vstart/power(2,24) = d.low/power(2,24)
group by
substring(d.name,1,12),
substring(d.phyname,1,17),
d.low/power(2,24),
(d.high-d.low+1)/512
order by
vdevno
go
grant execute on sp_freedisk to public
go
Tags:Useful StorePrecedure
编辑录入:coldstar [复制链接] [打 印]更多精彩
赞助商链接