SQL Server — Table size sp_msforeachtable

Sometimes we need information about a table size or a number of rows. We can use the sp_spaceused stored procedure.

USE StackOverflow
exec sp_spaceused 'dbo.Posts'

But if we need information about every table in a database? We can use an undocumented sp_msforeachtable stored procedure along with the previous one.

sp_msforeachtable 'exec sp_spaceused [?]'

To improve a readability we can insert result into a table, format values and change the size from KB to MB.

Here is an example.

IF OBJECT_ID('tempdb..#SpaceUsed') IS NOT NULL
DROP TABLE #SpaceUsed
GO

CREATE TABLE #SpaceUsed
(
 [name] VARCHAR(100), 
 [rows] BIGINT, 
 [reserved] VARCHAR(20),
 [data] VARCHAR(20),
 [index_size] VARCHAR(20),
 [unsed] VARCHAR(20)
)
GO

sp_msforeachtable 'INSERT INTO #SpaceUSed exec sp_spaceused [?]'
GO

SELECT 
 [name],
 FORMAT([rows], '#,#') as [rows],
 FORMAT(CONVERT(numeric(22, 2), REPLACE([reserved], ' KB', '')) / 1024, 'N2') AS [reserved (MB)],
 FORMAT(CONVERT(numeric(22, 2), REPLACE([data], ' KB', '')) / 1024, 'N2') AS [data (MB)],
 FORMAT(CONVERT(numeric(22, 2), REPLACE([index_size], ' KB', '')) / 1024, 'N2') AS [index_size (MB)],
 FORMAT(CONVERT(numeric(22, 2), REPLACE([unsed], ' KB', '')) / 1024, 'N2') AS [unsed (MB)]
FROM #SpaceUsed
GO

Here is a concise and more legible summary.