Field Sizes | SQL
Tags: dynamicnavbc, sql, mssql, query
Published: 2022-02-02 19:26:35

---
title: Field Sizes | SQL
published: true
date: 2021-12-08 12:05:55 UTC
tags: DynamicNAVBC,SQL,MSSQL,Query
canonical_url: 
---

 **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](https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-tables-transact-sql?view=sql-server-ver15)**.

```sql
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](https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-tables-transact-sql?view=sql-server-ver15)**. 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.

![](https://toolgbrmaker.files.wordpress.com/2021/12/image-17.png?w=548)

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:

```sql
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…

![](https://toolgbrmaker.files.wordpress.com/2021/12/image-20.png?w=1024)

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.

```sql
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.