데이터베이스 사용량
-- db size(MB)
SELECT SUM(CAST(df.size as float))/128 FROM sys.database_files AS df WHERE df.type in ( 0, 2, 4 );
-- log size(MB)
SELECT SUM(CAST(df.size as float))/128 FROM sys.database_files AS df WHERE df.type in ( 1,3 );
-- SpaceUsed(MB)
SELECT sum(total_pages)/128 FROM sys.allocation_units;
테이블 사용량
-- table used
declare @PageSize float
select @PageSize=v.low/1024.0 from master.dbo.spt_values v where v.number=1 and v.type='E'
SELECT
tbl.name,
ISNULL((select @PageSize * SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
where i.object_id = tbl.object_id),0.0) AS [IndexSpaceUsed],
ISNULL((select @PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
where i.object_id = tbl.object_id),0.0) AS [DataSpaceUsed]
FROM
sys.tables AS tbl
