Code SQL tính dung lượng table trong SQL

SELECT
    max(OBJECT_NAME(i.OBJECT_ID)) AS TableName,

   
    8 * SUM(a.used_pages) AS 'Indexsize(KB)'
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.is_primary_key = 0 -- fix for size discrepancy
GROUP BY
    i.OBJECT_ID
 
ORDER BY
    (8 * SUM(a.used_pages)) desc

CREATE TABLE #t (name SYSNAME, rows CHAR(11), reserved VARCHAR(18), 
data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18))

DECLARE @Sizes TABLE(name SYSNAME, rows int, reserved int, Data int, index_size int, unused int)

-- ensure correct values are being returned by using  @updateusage see http://msdn.microsoft.com/en-us/library/ms188776.aspx
EXEC sp_msforeachtable 'INSERT INTO #t EXEC sp_spaceused ''?'', @updateusage = N''TRUE'';'

INSERT INTO @Sizes
SELECT Name, Rows, CAST(SUBSTRING(Data, 1, LEN(Data)-3) as INT), CAST(SUBSTRING(Reserved, 1, LEN(Reserved)-3) as INT),
  CAST(SUBSTRING(Index_Size, 1, LEN(Index_Size)-3) as INT), CAST(SUBSTRING(Unused, 1, LEN(Unused)-3) as INT)
FROM #t

DROP TABLE #t

SELECT CAST(SUM(Data) as varchar)+' KB' as 'Data Size',
  CAST(SUM(Reserved) as varchar)+' KB' as 'Reserved Size',
  CAST(SUM(Index_Size) as varchar)+' KB' as 'Index Size',
  CAST(SUM(Unused) as varchar)+' KB' as 'Unused Size'
FROM @Sizes


SELECT *
FROM @Sizes
ORDER BY rows DESC

Code check dung lượng toàn bộ data trong SQL

SELECT DB_NAME(database_id) AS DatabaseName,
       Name AS Logical_Name,
       Physical_Name,
       (size * 8) / 1024 SizeMB
FROM sys.master_files order by sizemb desc

--WHERE DB_NAME(database_id) = 'MY_DB'