SQL Server - Determine How much disk space does database table consume

Sometimes we needs to calculate the disk space used by the each table in the database to know the highest weight table in the database. Following are the different methods for achieve this.

Method 1:
SQL Server provides a special procedure called  "sp_spaceused". Using this, we can get the space used by a table as below.

EXEC sp_spaceused  <Table Name>

To get the all the tables data we can use the following command

EXEC sp_msforeachtable 'sp_spaceused "?"'

The ? serves as a placeholder for each table name.
It is the fastest method.

Method 2:
Below query will give all the tables in highest used space wise

SELECT T.NAME AS TableName
,S.Name AS SchemaName
,P.Rows AS RowCounts
,SUM(A.total_pages) * 8 AS TotalSpaceKB
,SUM(A.used_pages) * 8 AS UsedSpaceKB
,(SUM(A.total_pages) - SUM(A.used_pages)) * 8 AS UnusedSpaceKB
FROM SYS.TABLES T
INNER JOIN SYS.INDEXES I ON T.object_id = I.object_id 
INNER JOIN SYS.PARTITIONS P ON I.object_id = P.object_id AND I.index_id = P.index_id
INNER JOIN SYS.ALLOCATION_UNITS A ON P.partition_id = A.container_id
LEFT OUTER JOIN SYS.SCHEMAS S ON T.schema_id = S.schema_id 
WHERE T.NAME NOT LIKE 'dt%'
    AND T.is_ms_shipped = 0
    AND I.object_id > 255
GROUP BY T.Name, S.Name, P.Rows
ORDER BY UsedSpaceKB DESC

Gopikrishna