Useful StorePrecedure

sp_disk

　　列出SQL Server中所有数据库设备的逻辑名，物理设备名，设备号，设备大小。

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中所有设备的逻辑名，物理设备名，设备号，设备大小，已经使用的设备空间，剩余设备空间。

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