CODEWITHSUNDEEP
sqlsql-servert-sql
ᗩИᎠЯƎᗩ
ᗩИᎠЯƎᗩ

Reputation: 2132

SQL Server: get a report on DB used and unused space

A customer asked me to create a query to create reports on database space usage, with details for every table.

Thinking it could be useful for others, I post here my solution.

Please post comments and possible improvements. I would like to know, for example, how to get the same results for MySql databases.

Upvotes: 0

Views: 1262

Answers (1)

ᗩИᎠЯƎᗩ
ᗩИᎠЯƎᗩ

Reputation: 2132

This query works on Microsoft Sql Server 2000+.

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

Upvotes: 1

Related Questions