Microsoft SQL Server how to get a table size
Sometimes you need to get the size of a particular table located in a database within the Microsoft Server and the following query can provide you that. You can run this manually or place it in a stored procedure for numerous tables.
The SQL code
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER
JOIN
sys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows ORDER BY t.Name
Looking at the actual code it displays the name of the table, schema name, record count (rows), size and additional information pertaining to the space used or not used.
This is a nifty way to quickly view which tables are large and growing. This provides analysis on the database giving a SQL admin a method to adjust what could happen in the future.
You can also place this in your source code to call the stored procedure returning the list of tables and their information. A simple task for everyone to grasp where their database is heading.