CREATE procedure [dbo].[db_space]
as
begin
declare @sql varchar(5000);
set @sql = 'use [?];declare @id int, @type character(2), @pages bigint, @dbname sysname, @dbsize bigint, @logsize bigint, @reservedpages bigint, @usedpages bigint, @rowCount bigint;
select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end)), @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) from dbo.sysfiles;
select @reservedpages = sum(a.total_pages), @usedpages = sum(a.used_pages), @pages = sum(CASE When it.internal_type IN (202,204) Then 0 When a.type <> 1 Then a.used_pages When p.index_id < 2 Then a.data_pages Else 0 END ) from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id left join sys.internal_tables it on p.object_id = it.object_id select d_name = db_name(), d_used = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize)) * 8192 / 1048576,15,2)), d_free = ltrim(str((case when @dbsize >= @reservedpages then (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) * 8192 / 1048576 else 0 end),15,2));'
if object_id('tempdb..##db_space_info') is not null
drop table ##db_space_info
create table ##db_space_info (
d_name varchar(50)
,d_used varchar(50)
,d_free varchar(50)
)
insert into ##db_space_info exec sp_msforeachdb @sql;
select * from ##db_space_info ORDER BY d_name;
end
최신 댓글