sys.dm_db_partition_stats DMV (Dynamic Management View), described by BOL as follows: http://msdn.microsoft.com/en-us/library/ms187737.aspx
Returns page and row-count information for every partition in the current database.
This DMV gives useful information about rowcount, free & reserved space of each table in database. It can be used for analysis purpose while adding new indexes, doing index Maintenance or checking the biggest table of database.
Query 1 : Details of table with row count & space used
SELECT
SS.NAME AS SCHEMANAME,
SO.NAME AS OBJECTNAME,
SI.NAME AS INDEXNAME,
PS.RESERVED_PAGE_COUNT*8 TOTAL_SPACE_CONSUMED_KB,
PS.USED_PAGE_COUNT*8 USED_SPACE_KB,
(PS.RESERVED_PAGE_COUNT – PS.USED_PAGE_COUNT)*8 FREE_SPACE_KB,
CASE
WHEN PS.INDEX_ID IN (0,1) THEN PS.ROW_COUNT
ELSE NULL
END AS ROW_COUNT
FROM
SYS.DM_DB_PARTITION_STATS PS
INNER JOIN SYS.OBJECTS SO ON SO.OBJECT_ID = PS.OBJECT_ID
INNER JOIN SYS.SCHEMAS SS ON SS.SCHEMA_ID = SO.SCHEMA_ID
LEFT JOIN SYS.INDEXES SI ON SI.OBJECT_ID = PS.OBJECT_ID
AND SI.INDEX_ID = PS.INDEX_ID
WHERE
SO.IS_MS_SHIPPED = 0
Sample Result
1. To use this DMV, User required VIEW DATABASE STATE permission on the server.
If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun
Reference : Rohit Garg (http://mssqlfun.com/)
