Get size of all tables in database

SELECT     t.NAME AS TableName,    s.Name AS SchemaName,    p.rows,    SUM(a.total_pages) * 8 AS TotalSpaceKB,     CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,    SUM(a.used_pages) * 8 AS UsedSpaceKB,     CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,     (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMBFROM     sys.tables tINNER JOIN          sys.indexes i ON t.OBJECT_ID = i.object_idINNER JOIN     sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_idINNER JOIN     sys.allocation_units a ON p.partition_id = a.container_idLEFT OUTER JOIN     sys.schemas s ON t.schema_id = s.schema_idWHERE     t.NAME NOT LIKE 'dt%'     AND t.is_ms_shipped = 0    AND i.OBJECT_ID > 255 GROUP BY     t.Name, s.Name, p.RowsORDER BY     TotalSpaceMB DESC, t.Name

If you are using SQL Server Management Studio (SSMS), instead of running a query (which in my case returned duplicate rows) you can run a standard report. 

1. Right click on the database
2. Navigate to Reports > Standard Reports > Disk Usage By Table

Note: The database compatibility level must be set to 90 or above for this to work correctly. See http://msdn.microsoft.com/en-gb/library/bb510680.aspx

sp_spaceused can get you information on the disk space used by a table, indexed view, or the whole database.

For example:

USE MyDatabase; GOEXEC sp_spaceused N'User.ContactInfo'; GO

This reports the disk usage information for the ContactInfo table.

To use this for all tables at once:

USE MyDatabase; GOsp_msforeachtable 'EXEC sp_spaceused [?]' GO

You can also get disk usage from within the right-click Standard Reports functionality of SQL Server. To get to this report, navigate from the server object in Object Explorer, move down to the Databases object, and then right-click any database. From the menu that appears, select Reports, then Standard Reports, and then "Disk Usage by Partition: [DatabaseName]".