|
제가 여기서 이런저런 도움을 많이 받았습니다. 그래서 제가 별로 아는건 없지만..
이것저것 짬뽕해서 만든 프로시져입니다. ASE 에서만 사용을 하시기 바랍니다.(12.5)
사족: 로직이 단순합니다. ㅡ,.ㅠ;
이상한 부분이 있으시면 알려주시기 바랍니다.
CREATE PROCEDURE sp_asedbchk
AS
DECLARE @d_name varchar(20)
DECLARE @d_size varchar(20)
DECLARE @d_dt varchar(14)
DECLARE @d_id int
DECLARE @dec_size decimal
SET NOCOUNT ON
select @d_name = NULL
select @d_size = NULL
select @d_dt = NULL
select @d_id = 0
create table #db_sized
(
name varchar(20) null,
size varchar(20) null,
usage varchar(20) null,
created varchar(14) null,
free varchar(20) null
)
declare get_dbname cursor FOR
select d.name as d_name,
str(u.size / 256, 10, 1) + " MB" as d_size,
d.dbid as d_id,
convert(char(14), d.crdate, 107) as d_dt
from master.dbo.sysdatabases d,
(select dbid, sum(size) as size from master.dbo.sysusages
group by dbid) u
where d.dbid = u.dbid
order by name
for read only
open get_dbname
FETCH get_dbname into @d_name, @d_size, @d_id, @d_dt
while @@sqlstatus = 0
begin
if @@sqlstatus = 1
begin
close get_dbname
deallocate cursor get_dbname
return
end
-- insert Database Name
insert into #db_sized
(name, size, usage, created, free)
values
(@d_name, @d_size, ' ', @d_dt, ' ')
-- insert segment Name & disk Usage
insert into #db_sized
select ' - '+ v.name,
str(u.size / 256, 10, 1) + " MB",
convert(char(20), m.description) ,
convert(char(19), u.crdate, 100) ,
case
when u.segmap = 4 then "not applicable"
else
str((curunreservedpgs(d.dbid, u.lstart,u.unreservedpgs) * 4), 16)
end
from master.dbo.sysdatabases d,
(select dbid, segmap, lstart, size, vstart,
unreservedpgs, crdate
from master.dbo.sysusages
where dbid = @d_id) u,
master.dbo.sysdevices v,
master.dbo.spt_values b,
master.dbo.sysmessages m
where d.dbid = u.dbid
and vstart between v.low and v.high
and v.status & 2 = 2
and d.name = @d_name
and b.type = "S"
and u.segmap & 7 = b.number
and b.msgnum = m.error
and isnull(m.langid, 0) = 0
select @dec_size = sum(convert(decimal(10), free))
from #db_sized
where usage like 'data%'
insert into #db_sized
(name, size, usage, created, free)
values
(' ▶ Free Byte Sum :', ' ', ' ', ' ', convert(char(20),@dec_size))
FETCH get_dbname into @d_name, @d_size, @d_id, @d_dt
end
close get_dbname
deallocate cursor get_dbname
select "Device Name" = name,
"Size" = size,
"Usage" = usage,
"Create Date" = created,
"Free Size" = free
from #db_sized
drop table #db_sized
|
