SQL SERVER 2012 Hosting :: How to find the size of all tables in SQL Server database

Roberto Paladino | November 20, 2014 | Other Related Posts, SQL 2008 Hosting, SQL 2008 R2 Hosting, SQL 2012 Hosting, SQL Azure, SSRS 2012 Hosting | No Comments

Find the size of all tables in SQL Server database

SQL Server gives you everything its stored procedure sp_spaceused. Unfortunately this SP does not support iterating over all tables in a database, so we needed to leverage another (undocumented) Stored Procedure sp_msForEachTable.

Here I will show how to find size of all table in SQL SERVER.

SET NOCOUNT ON
 
DBCC UPDATEUSAGE(0)
 
-- Find DB size.
EXEC sp_spaceused
 
-- Create a table to counts row and sizes.
CREATE TABLE #tbl
(
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)
 
INSERT #tbl EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''
 
SELECT * FROM #tbl
 
-- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM #tbl
 
DROP TABLE #tbl

SET NOCOUNT ON

DBCC UPDATEUSAGE(0)

-- Find DB size.

EXEC sp_spaceused

-- Create a table to counts row and sizes.

CREATE TABLE #tbl

(

[name] NVARCHAR(128),

[rows] CHAR(11),

reserved VARCHAR(18),

data VARCHAR(18),

index_size VARCHAR(18),

unused VARCHAR(18)

)

INSERT #tbl EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

SELECT * FROM #tbl

-- # of rows.

SELECT SUM(CAST([rows] AS int)) AS [rows]

FROM #tbl

DROP TABLE #tbl

After executing this, you will get the size of all tables in your database 😀 😀

SQL SERVER 2012 Hosting :: How to find the size of all tables in SQL Server database

Find the size of all tables in SQL Server database

About The Author

Ron