SQL database analysis

USE AdventureWorks2012;

SELECT  * ,
        Q.page_count * 8 / 1024 AS MB
FROM    ( SELECT    S2.[name] AS schema_name ,
                    T.[name] AS table_name ,
                    SUM(S.row_count) AS row_count ,
                    SUM(S.used_page_count) AS page_count ,
                    CASE SUM(S.used_page_count)
                      WHEN 0 THEN 0
                      ELSE SUM(S.row_count) / SUM(S.used_page_count)
                    END AS rows_per_page
          FROM      sys.dm_db_partition_stats S
                    INNER JOIN sys.tables T ON T.object_id = S.object_id
                    INNER JOIN sys.schemas S2 ON S2.schema_id = T.schema_id
          WHERE     ( S.index_id = 0
                      OR S.index_id = 1
                    )
          GROUP BY  S2.[name] ,
                    T.[name]
        ) Q
ORDER BY Q.page_count DESC;

Select all Open in new window