How to get list of tables, number of rows, data and index space

---

Download xSQL Compare Bundle, starting from $0!

---

Here is a simple query that returns the complete list of user tables on a SQL Server database - it includes the schema name, table name, the date on which the table was created, the number of rows, the disk space occupied by the data in KB and the disk space occupied by the indexes:

SELECT sschemas.name AS SchemaName
               ,sobjects.name AS TableName
               ,sobjects.create_date AS CreatedOn
               ,sobjects.modify_date AS ModifiedOn
               ,MAX(sstats.row_count) AS NoRows
               ,SUM(CASE
                               WHEN (sstats.index_id < 2)
                                       THEN sstats.in_row_data_page_count + sstats.lob_used_page_count +
                                                   sstats.row_overflow_used_page_count
                                        ELSE 0
                            END) * 8 AS DataSpaceKB
                 ,SUM(CASE
                                 WHEN (sstats.index_id >= 2) THEN sstats.used_page_count
                                 ELSE sstats.in_row_used_page_count - sstats.in_row_data_page_count
                   END) * 8 AS IndexSpaceKB
FROM sys.schemas AS sschemas
            INNER JOIN sys.objects AS sobjects ON sschemas.schema_id = sobjects.schema_id
            INNER JOIN sys.dm_db_partition_stats AS sstats ON sobjects.object_id = sstats.object_id
WHERE sobjects.type = 'U'
GROUP BY sschemas.name, sobjects.name, sobjects.create_date, sobjects.modify_date
ORDER BY sobjects.name

A couple of things you need to know in order to understand this:

• one page is equal to 8K in size 
• index_id values are 0 for the heap, 1 for the clustered index and > 1 for nonclustered indexes
• in_row_data_page_count represents the number of pages used for storing in-row data and it only includes the leaf pages
• lob_used_page_count represents the number of pages used for storing out of row text, n/varchar(max), n/varbinary(max), image, and xml columns.
• row_overflow_used_page_count represents the number of pages for storing row overflow
• in_row_used_page_count represents the total number of pages in use to store in-row data including both leaf and non-leaf pages. Hence, to determine the index space for the clustered index we subtract the in_row_data_page_count from the in_row_used_page_count to get only the non-leaf pages which should be attributed to the index space.

Any questions or comments please leave them here.

If you have not tried our SQL Server comparison download them now http://www.xsql.com/download/sql_server_comparison_bundle/ - quick and easy to install, even easier to use, fast, robust and completely free for SQL Server Express.