SQL Server Get the Size of Tables, Logs, and Indexes
Related to:: SQL Server
Size of .mdf and .ldf Files
SELECT
file_id,
name,
type_desc,
physical_name,
size / 128 sizeInMB,
max_size
FROM
sys.database_files;Procedure
-- Size of the database
sp_spaceused
-- Size of a table
sp_spaceused 'failed_jobs'Size of All / Some Tables and Indexes
To check for one or some tables, uncomment the where line and put the table names there.
WITH cte AS (
SELECT
t.name AS TableName,
SUM(s.used_page_count) AS used_pages_count,
SUM(
CASE WHEN (i.index_id < 2) THEN
(in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE
lob_used_page_count + row_overflow_used_page_count
END) AS pages
FROM
sys.dm_db_partition_stats AS s
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id]
AND s.index_id = i.index_id
-- WHERE
-- t.name in('failed_jobs')
GROUP BY
t.name
)
SELECT
cte.TableName,
cast((cte.pages / 128.) AS decimal (10, 3)) AS TableSizeInMB,
cast(((
CASE WHEN cte.used_pages_count > cte.pages THEN
cte.used_pages_count - cte.pages
ELSE
0
END) / 128.) AS decimal (10, 3)) AS IndexSizeInMB
FROM
cte
ORDER BY
2 DESC