THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Sergio Govoni

A place where share my experience on SQL Server and related technologies

The biggest table in your database

You may need to know which is the biggest table (in terms of disk space usage) in the database that you are currently connected. Sometimes, this information is very useful to check the indexing strategy of these tables.
There are several methods to know which is the biggest table of a database, one of these methods is to use the standard report "Disk Usage by Top Table" exposed by SQL Server Management Studio. Another method is through T-SQL language, you can perform the sp_spaceused system stored procedure for each table contained in your database, you could store all the partial results in a temporary table and ordering these results by the "data" column, so you can find the biggest table of the database.
Using the sp_spaceused system stored procedure we have to accept a row-by-row solution, in fact, we will perform a call to sp_spaceused for each table contained into the database. Increasing the number of tables, the number of calls to the stored procedure will grow up.
An alternative solution is represented by the following Common Table Expression based on the internal code of the sp_spaceused system stored procedure, it allows us to obtain the result with a single execution, in a set-based way.
WITH spaceused AS
    ,reservedpages = SUM(reserved_page_count)
    ,it_reservedpages = SUM(ISNULL(its.it_reserved_page_count, 0))
    ,usedpages = SUM(used_page_count)
    ,it_usedpages = SUM(ISNULL(its.it_used_page_count, 0))
    ,pages = SUM(CASE
                   WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
                   ELSE lob_used_page_count + row_overflow_used_page_count
    ,row_Count = SUM(CASE WHEN (index_id < 2) THEN row_count ELSE 0 END)
    sys.objects ON sys.objects.object_id=sys.dm_db_partition_stats.object_id
       reserved_page_count AS it_reserved_page_count
       ,used_page_count AS it_used_page_count
       sys.internal_tables AS it
     it.parent_id = object_id
     AND it.internal_type IN (202,204,211,212,213,214,215,216)
     AND object_id = it.object_id
  ) AS its
    sys.objects.type IN ('U', 'V')
  name = OBJECT_NAME (object_id)
  ,rows = convert (char(11), row_Count)
  ,reserved = LTRIM (STR (reservedpages * 8, 15, 0) + ' KB')
  ,it_reserved = LTRIM (STR (it_reservedpages * 8, 15, 0) + ' KB')
  ,tot_reserved = LTRIM (STR ( (reservedpages + it_reservedpages) * 8, 15, 0) + ' KB')
  ,data = LTRIM (STR (pages * 8, 15, 0) + ' KB')
  ,data_MB = LTRIM (STR ((pages * 8) / 1000.0, 15, 0) + ' MB')
  ,index_size = LTRIM (STR ((CASE WHEN usedpages > pages THEN (usedpages - pages) ELSE 0 END) * 8, 15, 0) + ' KB')
  ,it_index_size = LTRIM (STR ((CASE WHEN it_usedpages > pages THEN (it_usedpages - pages) ELSE 0 END) * 8, 15, 0) + ' KB')
  ,tot_index_size = LTRIM (STR ((CASE WHEN (usedpages + it_usedpages) > pages THEN ((usedpages + it_usedpages) - pages) ELSE 0 END) * 8, 15, 0) + ' KB')
  ,unused = LTRIM (STR ((CASE WHEN reservedpages > usedpages THEN (reservedpages - usedpages) ELSE 0 END) * 8, 15, 0) + ' KB')
  pages DESC;
Are you thinking how can we access to the internal code of the sp_spaceused? It is simple, we can do that using the sp_helptext system stored procedure!
Have fun! 
Published Friday, February 12, 2016 1:34 AM by Sergio Govoni

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS


No Comments

Leave a Comment


This Blog


Privacy Statement