Get size of tables from SQL Data base for D365FO

Hi All,

In one of the projects, we got the requirements from client for analyze the size of the data after upload all the data, so that they can understand what the size of the data could be after 5-10 years.

1. Get size of all tables in database   

   This will help to get the complete list of tables with records and size.

   SELECT 

    t.name AS TableName,

    s.name AS SchemaName,

    p.rows,

    SUM(a.total_pages) * 8 AS TotalSpaceKB, 

    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,

    SUM(a.used_pages) * 8 AS UsedSpaceKB, 

    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 

    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,

    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB

    FROM 

    sys.tables t

    INNER JOIN      

    sys.indexes i ON t.object_id = i.object_id

    INNER JOIN 

    sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id

    INNER JOIN 

    sys.allocation_units a ON p.partition_id = a.container_id

    LEFT OUTER JOIN 

    sys.schemas s ON t.schema_id = s.schema_id

    WHERE 

    t.name NOT LIKE 'dt%' 

    AND t.is_ms_shipped = 0

    AND i.object_id > 255 

    GROUP BY 

    t.name, s.name, p.rows

    ORDER BY 

    TotalSpaceMB DESC, t.name

Output : 18210 rows

2. Get size of multiple tables in database

 This will help to get the complete list of required multiple tables with records and size.

SELECT 

    t.name AS tablename, --INVENTTABLE,
    s.name AS SchemaName, --NVENTTABLE,
    p.rows,    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS              UnusedSpaceMB FROM 
    sys.tables t INNER JOIN          sys.indexes i ON t.object_id = i.object_id INNER JOIN 
    sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id WHERE 
    t.name in ('InventTable',
                    'InventTableModule',
                    'MCRInventTable',
                    'PdsBatchAttribByItem',
                    'RetailInventTable',
                    'TMSInventEnabled',
                    'WHSInventEnabled',
                    'WHSInventTable',
                    'WHSPhysDimUOM')

            AND t.is_ms_shipped = 0 AND i.object_id > 255 GROUP BY 
            t.name, s.name, p.rows
    ORDER BY 
        TotalSpaceMB DESC, t.name

 Output: 13 rows

3.Get size of single table in database

This will help to get the complete required details of the tables with records and size.

SELECT 

    t.name AS TableName,

    s.name AS SchemaName,

    p.rows,

    SUM(a.total_pages) * 8 AS TotalSpaceKB, 

    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,

    SUM(a.used_pages) * 8 AS UsedSpaceKB, 

    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 

    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,

    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB

FROM 

    sys.tables t

INNER JOIN      

    sys.indexes i ON t.object_id = i.object_id

INNER JOIN 

    sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id

INNER JOIN 

    sys.allocation_units a ON p.partition_id = a.container_id

LEFT OUTER JOIN 

    sys.schemas s ON t.schema_id = s.schema_id

WHERE 

    t.name LIKE 'CustTable' 

    AND t.is_ms_shipped = 0

    AND i.object_id > 255 

GROUP BY 

    t.name, s.name, p.rows

ORDER BY 

    TotalSpaceMB DESC, t.name

Output:

Thanks & Regards

Naresh Dharanikota