How do I see the size of all the tables

Question

I've created a table in my database by mistake which has zero rows, and I want to know a systemic way to find out that table. I know we can use sp_spaceused to get the number of rows but how can I do it for all the tables.

Does this answer your question? [Query to list number of records in each table in a database](https://stackoverflow.com/questions/1443704/query-to-list-number-of-records-in-each-table-in-a-database) — SMor, Jul 09 '20 at 22:19
Hello @SMor The link that you provided does provide the answer to what I was looking for, but the question is not correct. I was also thinking to have a specific question as it makes it easier for us to be sure which question to look into while searching for answers. — Anurag Agrawal, Jul 15 '20 at 16:39
@SMor i do see another question which is same as mine - https://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database — Anurag Agrawal, Jul 15 '20 at 16:42

score 0 · Accepted Answer · edited Jul 09 '20 at 21:44

0

Paste the output of the below query in a new SQL Editor and do a visual scan of the output

SELECT *
FROM (
    SELECT 1000 * ROW_NUMBER () OVER (ORDER BY name) rk,
        ' sp_spaceused ' + name spused
    FROM sys.Tables
    UNION ALL
    SELECT 1 + 1000 * ROW_NUMBER () OVER (ORDER BY name), ' go'
    FROM sys.Tables
) a
ORDER BY a.rk

edited Jul 09 '20 at 21:44

Dale K

25,246
15
42
71

answered Jul 09 '20 at 21:35

Anurag Agrawal

1
2

Gabriele Franco · Answer 2 · 2020-07-09T23:20:31.617

Below you can find the query with additional details

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT  
    CASE WHEN FG.Name IS NULL THEN DS.Name ELSE FG.Name END      AS 'FileGroupName'
    ,DB_NAME()                                                   AS 'DatabaseName'
    ,OBJECT_SCHEMA_NAME(P.OBJECT_ID)                             AS 'Schema'
    ,OBJECT_NAME(P.OBJECT_ID)                                    AS 'TableName'
    ,CASE WHEN P.index_id = 0 THEN 'HEAP' ELSE I.name END        AS 'IndexName'
    ,CASE WHEN P.index_id IN (0,1) THEN 'data' ELSE 'index' END  AS 'Type'
    ,P.partition_number                                          AS 'PartitionNumber'
    ,CAST(P.reserved_page_count * 0.008 AS NUMERIC(18,2))        AS 'ReservedSize_MB'
    ,CAST(P.used_page_count * 0.008 AS NUMERIC(18,2))            AS 'UsedSize_MB'
    ,P.row_count                                                 AS 'RowCount'
FROM          
   sys.dm_db_partition_stats   P        
    INNER JOIN      sys.indexes                 I       ON I.OBJECT_ID = P.OBJECT_ID AND I.index_id = P.index_id
    INNER JOIN      sys.data_spaces             DS      ON DS.data_space_id = I.data_space_id
   LEFT OUTER JOIN sys.filegroups              FG      ON FG.data_space_id = DS.data_space_id
WHERE
    OBJECTPROPERTY(p.OBJECT_ID, 'ISMSSHipped') = 0
ORDER BY ReservedSize_MB DESC

Below you can find the query for details of partitioned tables

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT 
   DB_NAME()                                                    AS 'DatabaseName' 
       ,OBJECT_NAME(p.OBJECT_ID)                                    AS  'TableName' 
       ,p.index_id                                                  AS 'IndexId'                                    
   ,CASE                                                              
       WHEN p.index_id = 0 THEN 'heap'                          
       ELSE i.[name]                                                
   END                                                          AS 'IndexName' 
   ,sp.data_compression_desc                                    
   ,p.partition_number                                          AS 'PartitionNumber' 
   ,prv_left.[value]                                            AS 'LowerBoundary' 
   ,prv_right.[value]                                           AS 'UpperBoundary' 
   ,CASE
       WHEN fg.[name] IS NULL THEN ds.[name]
       ELSE fg.[name]
   END                                                          AS 'FileGroupName' 
   ,CAST(p.used_page_count * 0.0078125        AS numeric(18,2)) AS 'UsedPages_MB' 
   ,CAST(p.in_row_data_page_count * 0.0078125 AS numeric(18,2)) AS 'DataPages_MB' 
   ,CAST(p.reserved_page_count * 0.0078125    AS numeric(18,2)) AS 'ReservedPages_MB' 
   ,p.row_count                                                 AS 'RowCount' 
   ,CASE
        WHEN p.index_id IN (0, 1) THEN 'data'
        ELSE 'index'
    END 'Type'
FROM 
    sys.dm_db_partition_stats p
    INNER JOIN sys.indexes i                             ON i.OBJECT_ID = p.OBJECT_ID
                                                         AND i.index_id = p.index_id
    INNER JOIN sys.partitions sp                         ON p.[partition_id] = sp.[partition_id]
    INNER JOIN sys.data_spaces ds                        ON ds.data_space_id = i.data_space_id
    LEFT OUTER JOIN sys.partition_schemes ps             ON ps.data_space_id = i.data_space_id
    LEFT OUTER JOIN sys.destination_data_spaces dds      ON dds.partition_scheme_id = ps.data_space_id
                                                         AND dds.destination_id = p.partition_number
    LEFT OUTER JOIN sys.filegroups fg                    ON fg.data_space_id = dds.data_space_id
    LEFT OUTER JOIN sys.partition_range_values prv_right ON prv_right.function_id = ps.function_id
                                                         AND prv_right.boundary_id = p.partition_number
    LEFT OUTER JOIN sys.partition_range_values prv_left  ON prv_left.function_id = ps.function_id
                                                         AND prv_left.boundary_id = p.partition_number - 1
WHERE 
    OBJECTPROPERTY(p.OBJECT_ID, 'ISMSSHipped') = 0
ORDER BY 2,3,5

How do I see the size of all the tables

2 Answers2