Dynamics NAV Overview of
installed Sector
and Specialized Solutions

SQL-Skript

In Dynamics NAV, one or more version numbers are assigned to the objects.

However, to get a quick overview of all installed sector and specialized solutions, a glance at these version numbers is not enough, as we are talking about thousands of entries here. The following small SQL script provides a remedy.

DECLARE @dbname varchar(20);
SET @dbname = 'INSERT_DBNAME';
DECLARE @stmt varchar(612);
SET @stmt =
'WITH sortedList AS
(
  SELECT
    SUBSTRING(vList.Item,0,PATINDEX(''%[0-9]%.%'',vList.Item)) AS Produkt,
    SUBSTRING(vList.Item,PATINDEX(''%[0-9]%.%'',vList.Item),LEN(vList.Item)) AS allVersions
    FROM
    (
      SELECT Item
      FROM DelimitedSplit8K(
        STUFF(
            (
            SELECT DISTINCT '','' + OBJ.[Version List] AS [text()]
            FROM [' + @dbname + '].[dbo].[Object] OBJ
            FOR XML PATH('''')
            )
          ,1,1,'''')
        ,'','')
      WHERE Item != ''''
    ) AS vList
)
SELECT sortedList.Produkt,MAX(sortedList.allVersions) AS Version
  FROM sortedList
  WHERE Produkt != ''''
  GROUP BY sortedList.Produkt';
EXEC(@stmt);

The “DelimitedSplit8K” function used in the SQL script is based on “Tally-Tables” and is the best-performing of the generally known SQL splitter methods. The function can be downloaded here and must – ideally in the Master DB – be created beforehand.

This SQL query first creates a comma-separated list of all version entries. The elements separated by commas are divided by the “DelimitedSplit8K” function and fed into a temporary table. This table then contains all individual product versions of the entire “Object” table. For our purposes, however, we are only interested in the latest versions of a product and ultimately only output these. In addition, the product name is separated from the version number.

When the SQL script is executed, a list appears with all solutions installed in the database.

It should be mentioned that the output is not completely error-free. Due to the unfortunate naming of the product “NAVW1” with the subsequent version number and the lack of possibilities to filter this out in addition, it should be noted that in this case there is an additional “1” in front of the version number.

In the second line of the SQL script, the database name of the database to be checked must be entered in the variable. We have marked this position with INSERT_DBNAME.