SQL Server Database analysis using T-SQL

Question

I have a small application which has around 38 tables in the Database (SQL Server 2000/2005). Now i would like to know how much data (in KB's/MB's not records) they are taking using T-SQL.
Saying in better way i would like to do analysis of entire database. Is that possible? How ? Thanks All,

Answer 1

for the whole database you can just run this

exec sp_helpdb 'YourDatabaseName'

per table you can use this (2005+)

declare @PageSize float
select @PageSize=v.low/1024.0 from master.dbo.spt_values v 
where v.number=1 and v.type='E'

SELECT tbl.name,

ISNULL((select @PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages 
WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)

FROM sys.indexes as i

JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id

JOIN sys.allocation_units as a ON a.container_id = p.partition_id

where i.object_id = tbl.object_id),0.0) AS [DataSpaceUsed_KB],

ISNULL((select @PageSize * SUM(a.used_pages - CASE WHEN a.type <> 1 
THEN a.used_pages      WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)

FROM sys.indexes as i

JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id

JOIN sys.allocation_units as a ON a.container_id = p.partition_id

where i.object_id = tbl.object_id),0.0) AS [IndexSpaceUsed_KB]

FROM

sys.tables AS tbl

Answer 2

If you want to find the space of each table then you can use the following:

USE yourdbname
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

For further reading check out Displaying the Size of your SQL Server Database's Tables