Analyzing a SQL Server Database Using T-SQL

Question

I have a small application that contains about 38 tables in a database (SQL Server 2000/2005). Now I would like to know how much data (in KB / MB is not written) that they use using T-SQL.
Better to say, I would like to do an analysis of the entire database. Is it possible? How? Thanks everyone

Answer 1

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

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

For further reading, see Mapping SQL Server Database Tables

Answer 2

for the whole database you can just run this

 exec sp_helpdb 'YourDatabaseName' 

for the 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