CODEWITHSUNDEEP
sqlsql-server
TZ100
TZ100

Reputation: 318

How to analyze tables for size in a single DB using SQL Server?

I have a DB called Johnson containing 50 tables. The team that uses this DB has asked if I can provide them with a size estimate for each table

How can I display a list of all the tables within the DB and their respective size in GB or MB using SQL Server?

Upvotes: 7

Views: 8154

Answers (3)

Mockingbird
Mockingbird

Reputation: 1031

@M.Ali

I tried to run your script from SQL Server Management Studio on remote DB (from Azure), but it can't find the stored procedure.

Could not find stored procedure 'sp_MSforeachtable'.

If someone wants to run it on Azure DB, first run this script that creates sp_MSforeachtable stored procedure:

https://gist.github.com/metaskills/893599

Upvotes: 1

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

You could use SSMS to display the size of biggest 1000 tables thus:

enter image description here

Upvotes: 14

M.Ali
M.Ali

Reputation: 69524

SET NOCOUNT ON 

DBCC UPDATEUSAGE(0) 

-- DB size.
EXEC sp_spaceused

-- Table row counts and sizes.
CREATE TABLE #t 
( 
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18), 
    data VARCHAR(18), 
    index_size VARCHAR(18),
    unused VARCHAR(18)
) 

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' 

SELECT *
FROM   #t

-- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM   #t

DROP TABLE #t

When ran In a database context give results something like this.. Northwind Database enter image description here

Upvotes: 7

Related Questions