XML FeedsI do not know if there is already code for table sizes (as used in SQL Server 2008 reports) but what I've seen on the net people are using cursors and temporary tables (completely unnecessary).
NOTE (added later):
In order to update table sizes in sys.dm_db_partition_stats you can execute next DBCC (all tables for current database):
DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS, COUNT_ROWS
sql
- ;WITH SpaceUsage AS
- (
- SELECT s.Name + '.' + o.Name AS tblName,
- case when i.index_id < 2 Then row_count else 0 end AS nRows,
- reserved_page_count AS Reserved,
- used_page_count AS Used,
- case when i.index_id < 2 Then (in_row_used_page_count - in_row_data_page_count)
- else p.used_page_count end AS indUsed
- FROM sys.dm_db_partition_stats p
- INNER JOIN sys.objects o ON o.object_id = p.object_id
- INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
- LEFT OUTER JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
- WHERE o.type = 'U'
- AND o.is_ms_shipped = 0
- )
- SELECT tblName AS [TABLE Name],
- sum(nRows) AS [#Records],
- sum(Reserved) * 8 AS [Reserved(KB)],
- sum(Used-indUsed) * 8 AS [DATA(KB)],
- sum(indUsed) * 8 AS [Indexes(KB)],
- sum(Reserved-used) * 8 AS [Unused(KB)]
- FROM SpaceUsage
- GROUP BY tblName
- ORDER BY [Reserved(KB)] DESC
Here is a version with more details.
sql
- ;WITH SpaceUsage AS
- (
- SELECT s.Name + '.' + o.Name AS tblName,
- Case When i.index_id = 0 then 0 else 1 end AS nInd,
- case when i.index_id < 2 Then row_count else 0 end AS nRows,
- used_page_count AS Used,
- reserved_page_count AS Reserved,
- case when i.index_id <= 1 Then in_row_data_page_count else 0 end AS rowUsed,
- case when i.index_id <= 1 Then (in_row_used_page_count - in_row_data_page_count)
- else 0 end AS cliUsed,
- case when i.index_id < 1 Then p.used_page_count else 0 end AS indUsed,
- case when i.index_id <= 1 Then lob_used_page_count else 0 end AS lobUsed,
- case when i.index_id <= 1 Then row_overflow_used_page_count else 0 end AS ofwUsed,
- case when i.index_id <= 1 Then in_row_reserved_page_count else 0 end AS rowRsvd,
- case when i.index_id <= 1 Then row_overflow_reserved_page_count else 0 end AS ofwRsvd,
- case when i.index_id <= 1 Then lob_reserved_page_count else 0 end AS lobRsvd,
- case when i.index_id > 1 Then reserved_page_count else 0 end AS indRsvd
- FROM sys.dm_db_partition_stats p
- INNER JOIN sys.objects o ON o.object_id = p.object_id
- INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
- LEFT OUTER JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
- WHERE o.type = 'U'
- AND o.is_ms_shipped = 0
- )
- SELECT tblName,
- Sum(nInd) AS nInd,
- sum(nRows) AS nRows,
- sum(Used) * 8 AS UsedKB,
- case when sum(nRows)>0 Then (sum(Used)*8192)/Sum(nRows) else NULL end AS avgBPR,
- sum(Reserved) * 8 AS ReservedKB,
- sum(rowUsed) * 8 AS rowUsedKB,
- sum(cliUsed) * 8 AS cliUsedKB,
- sum(indUsed) * 8 AS indUsedKB,
- sum(lobUsed) * 8 AS lobUsedKB,
- sum(ofwUsed) * 8 AS ofwUsedKB,
- sum(rowRsvd) * 8 AS rowRsvdKB,
- sum(ofwRsvd) * 8 AS ofwRsvdKB,
- sum(lobRsvd) * 8 AS lobRsvdKB,
- sum(indRsvd) * 8 AS indRsvdKB
- FROM SpaceUsage
- GROUP BY tblName
- ORDER BY ReservedKB DESC
Congrats on a first blog!!!! 
This is very useful Nikola, thanks for posting this! 
Thanks buddy 
Nikola, one thing is that this table space information will usually be outdated unless you run sp_spaceused first, with 'updateusage' as the second parameter value.
If you want up to date statistics, it should be enough to run:
You appear to be omitting "indexed views" which can also use a substantial portion of the space.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.