Database Analysis Report (SQL Server)

Database-Analysis-Report.sql

--Database Analysis Report for SQL Server

--Author: M.Ali
--Source: http://stackoverflow.com/questions/19433902/how-to-analyze-tables-for-size-in-a-single-db-using-sql-server

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
ORDER BY name, index_size

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

DROP TABLE #t