Field Sizes | SQL

Version: SQL Server 2019

Recently, from a 3rd party software, I got the query of which was the sizes of some of the fields from an entity that they were using to execute some of the integrations. Obviously, they were trying to avoid the loss of data after the integration flow.

To avoid an endless request/reply moment (asking one field on each time), between me and the other side developer, I’ve created a little tool to easily get this information and share it. Is a simple tool, built on the SQL side, that could be easily refined and reutilized if needed.

First thing first, what we need is to get our entity on the system, but here we are looking for the entity schema, and not for the table itself. Therefore, to achieve it, I started by querying the sys.tables.

SELECT RIGHT(t.name,LEN(t.name) - charindex('$',t.name)) as TableName
    FROM sys.tables t
    WHERE t.type = 'U' and t.name like '%$Customer'

With the above code, we’ll get our entity row on sys.tables. See also, that we manage in the way to take off the Company Name from the table name. It will look more cleaner in this way.

Now that we have got the entity we need to get the table columns data to look for the field sizes. To achieve it we have the following:

SELECT RIGHT(t.name,LEN(t.name) - charindex('$',t.name)) as TableName,C.name, C.max_length as FieldSize
    FROM sys.tables t
    INNER JOIN sys.columns C ON C.object_id = t.object_id
    WHERE t.type = 'U' and t.name like '%$Customer' and C.name <> 'timestamp'

You can also notice above that we took off the Timestamp field since is not necessary for our end purpose. So far so good, but…

If you look closer you’ll see that a discrepancy exists between the data schema we are getting from SQL and what we have on the Business Central side and this is due to the use of nvarchar type on the SQL side. This type uses 2 bytes for each char and taking that into the consideration we ended with the following query.

SELECT RIGHT(t.name,LEN(t.name) - charindex('$',t.name)) as TableName,C.name,
    (CASE 
        WHEN C.system_type_id = 231 THEN -- NVARCHAR takes 2 bytes each
            (C.max_length / 2)
        ELSE
            C.max_length
    END
    )as FieldSize
    FROM sys.tables t
    INNER JOIN sys.columns C ON C.object_id = t.object_id
    WHERE t.type = 'U' and t.name like '%$Customer' and C.name <> 'timestamp'

With this tool, next time we’ll faster answer some similar size queries that may come outside. I hope that this can help you too in some way.

The above query has a lot of possibilities to be refined, so take your hands on it and share your customizations.