SQL script that retrieves information about field sizes in a table for Microsoft Dynamics NAV or Microsoft Dynamics 365 Business Central

Recently, I had a task in the Dynamics NAV version to identify fields size in one table because it was no longer possible to add new custom fields (some fields I had to migrate to another table while I had to define them as FlowFields in the main table).

Max record size in Dynamics NAV is 4KB, while in Dynamics Business Central it is slightly larger at 8.06KB.

To extract the length of the field as well as the data type, I created a SQL script, I hope that someone else will need this because I didn’t find it (it can be used for any version of Dynamics NAV or Business Central and for any table).

--- replace your DB name
DECLARE @dbname sysname = 'Demo Database BC (23-0)'
--- replace your the table name
DECLARE @tableName NVARCHAR(100) = 'CRONUS International Ltd_$Sales Header$437dbf0e-84ff-417a-965d-ed2bb9650972'
DECLARE @sqlQuery NVARCHAR(MAX)

SET @sqlQuery = '
    SELECT
		o.name AS TableName,
        c.name AS FieldName,
        t.name AS DataType,
        c.max_length AS Length
    FROM ' + QUOTENAME(@dbname) + '.sys.columns c
    JOIN ' + QUOTENAME(@dbname) + '.sys.objects o ON o.object_id = c.object_id
    JOIN sys.types t ON c.system_type_id = t.system_type_id
    WHERE o.type = ''U''
      AND o.name = ' + QUOTENAME(@tableName, '''') + '
      AND t.name <> ''sysname''
    ORDER BY c.max_length DESC, o.name, c.name
'
EXEC sp_executesql @sqlQuery

1. The script begins by declaring variables for the database name (@dbname) and the table name (@tableName).
2. It constructs a dynamic SQL query (@sqlQuery) to retrieve specific details about fields within the specified table.
3. The query selects the following information:
   • TableName: The name of the table.
   • FieldName: The name of the field/column.
   • DataType: The data type of the field.
   • Length: The maximum length (in characters) allowed for the field.
4. The script joins system tables (sys.columns, sys.objects, and sys.types) to gather relevant information.
5. It filters results to include only user-defined tables (o.type = ‘U’) and excludes fields with the data type ‘sysname’.
6. The results are ordered by maximum field length (c.max_length), table name (o.name), and field name (c.name).

Note: in output query there is no FlowFields because they are not available at SQL side.

To view field sizes in tables, from release 2020 of Dynamics 365 Business Central, follow these best practices:

1. Table Information Page:
   • The 8700 Table Information page provides valuable insights into the system and business tables in Business Central.
   • It displays the following information for each table:
     • Company Name: The company to which the table belongs (if any).
     • Table Name: The name of the table.
     • Table No.: The table’s unique identifier.
     • No. of Records: The total number of records stored in the table.
     • Record Size: The average record size in KB per record (calculated as 1024 (Size) / (No. of Records)).
     • Size (KB): The total space occupied by the table in the database (sum of Data Size and Index Size).
     • Data Size (KB): Space occupied by the data within the table.
     • Index Size (KB): Space occupied by table indexes (keys).
     • Compression: Indicates the type of compression applied to the table (Row, Page, or None).
   • To access this page:
     • Click the icon.
     • Enter Table Information.
     • Choose the related link.
   • Note that the values on this page may not update immediately after data deletion due to background processes.
2. If you are using lates version of BC with Dataverse you can check table sizes:
   • Capacity Page in Power Platform Admin Center
     • Follow these steps:
       • Log in to the Power Platform Admin Center.
       • Under Resources, click Capacity.
       • Select the Dataverse tab (or the Trial tab for trial versions).
       • Check the detailed report for Usages to see the size of each table in megabytes.

Remember that the Table Information page shows data and index sizes, but the sum of table sizes won’t match the total capacity used because it displays data size, not the actual allocated size. Allocated space is always larger than used space to optimize performance.

Learn, troubleshoot and enjoy. 🙂