개발 참고자료

CREATE TABLE #DiskUpsageByTables (
name NVARCHAR(128),
rows CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
);


INSERT INTO #DiskUpsageByTables
EXEC sp_MSforeachtable 'sp_spaceused ''?''';


SELECT *
FROM #DiskUpsageByTables
ORDER BY CAST(REPLACE(reserved, ' KB', '') AS INT) DESC;


DROP TABLE #DiskUpsageByTables;






========================================================================================




-- 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