Table Stats for Sql Server 2008

Table_Rows_DataSize_SQLServer2008.sql

SELECT
	o.[name] AS [Table_Name]
	, rc.[rows] AS [Data_Rows]
	, rc.[rows] / CASE WHEN rc.[usedPages] = 0 THEN 1 ELSE CAST(rc.[usedPages] AS FLOAT) END AS [Rows_Per_Page]
	, ic.[Indexes] AS [DeclaredIndexes]
	, ROUND(rc.[usedPages] * 8  / 1024.0, 2) AS [Data_Size_MB]
	, ROUND(ISNULL(ic.[usedPages] * 8, 0) / 1024.0, 2) AS [Index_size_MB]
FROM
	sys.objects AS o
	
	-- Row Counts
	CROSS APPLY ( 
		SELECT
			SUM ([row_count]) AS [rows]
			, SUM([used_page_count]) AS [usedPages]
		FROM sys.dm_db_partition_stats AS ss
		WHERE
			ss.[object_id] = o.[object_Id]
			AND ss.[index_id] IN (0, 1)
		)
		AS rc
	
	-- Index Stats
	OUTER APPLY ( 
		SELECT
			COUNT(*) AS [Indexes]
			, SUM ([row_count]) AS [rows]
			, SUM([used_page_count]) AS [usedPages]
		FROM sys.dm_db_partition_stats AS ss
		WHERE
			ss.[object_id] = o.[object_Id]
			AND ss.[index_id] > 1
		)
		AS ic
WHERE
	o.[type] = 'U'
	AND o.[is_ms_shipped] = 0
	AND rc.[rows] > 0
ORDER BY
	rc.[rows] DESC
	, o.[name]