Example: Finding the geodatabase release using SQL

You can directly query the version geodatabase system table in the database using SQL to determine the geodatabase release. This method allows you to determine the ArcGIS Pro version used to create or upgrade the geodatabase as well.

If you don't need the ArcGIS Pro version and you want to query the XML information for the geodatabase release, you can do that too.

Query the version table

To determine the geodatabase release, use SQL to query the version table (sde_version in Microsoft SQL Server and PostgreSQL).

Release numbers are stored in multiple fields in the version table. This table contains major, minor, and bug fix versions as well as fields that store the full geodatabase release.

The major version corresponds to the major release of the geodatabase. The minor version is signified by the number to the right of the dot (.) in the ArcGIS release. The bug fix version increases if you have installed a service pack, patch, or hot fix that resulted in changes to the geodatabase schema or you installed a bug fix release. For example, the major version number for 10.6.1 is 10, the minor version number is 6, and the bug fix number is 1.

If you create or upgrade the geodatabase from ArcGIS Pro, the description field will show a concatenation of major, minor, bug fix versions, and the ArcGIS Pro version from which you created or upgrade the geodatabase. If you do not create or upgrade the geodatabase from ArcGIS Pro, the description field will show zeros in place of an ArcGIS Pro version number.

To find the full geodatabase release, query the description column of the version table.

-- Queries an sde-schema geodatabase in SQL Server
-- Returns the geodatabase release from the sde_version table.

SELECT Description AS "Geodatabase release"
  FROM sde.sde_version

-- Queries a geodatabase in PostgreSQL
-- Returns the geodatabase release from the sde_version table.

SELECT description AS "Geodatabase release"
  FROM sde.sde_version;

-- Queries a geodatabase in Oracle
-- Returns the geodatabase release from the version table.

SELECT DESCRIPTION AS "Geodatabase release"
  FROM sde.version;

Query an XML document to determine the geodatabase release

You can extract the release number values from an XML document to discover the release of a specific geodatabase.

Release numbers are stored as major, minor, and bug fix versions. In XML, the major version is a numeric code that corresponds to the major release of the geodatabase. ArcGIS 8 was the first release to include ArcGIS geodatabase functionality; therefore, ArcGIS 8 corresponds to major version 1. ArcGIS 9 corresponds to major version 2, and ArcGIS 10 corresponds to major version 3.

The minor version is signified by the number to the right of the dot (.) in the ArcGIS release. For example, the minor version number for 10.6 is 6.

The bug fix version increases if you have installed a service pack, patch, or hot fix that resulted in changes to the geodatabase schema or you installed a bug fix release. For example, the bug fix version number for 10.6.1 is 1.

The following queries retrieve the major, minor, and bug fix version numbers of a geodatabase:

-- Queries a dbo-schema geodatabase in SQL Server
-- Gets the geodatabase release from the workspace catalog item.

SELECT
  Definition.value('(/DEWorkspace/MajorVersion)[1]', 'smallint') AS "Major version",
  Definition.value('(/DEWorkspace/MinorVersion)[1]', 'smallint') AS "Minor version",
  Definition.value('(/DEWorkspace/BugfixVersion)[1]', 'smallint') AS "Bug fix version"
FROM
 dbo.gdb_items AS items INNER JOIN
  (SELECT UUID 
   FROM dbo.gdb_itemtypes
   WHERE Name = 'Workspace') AS itemtypes
  ON items.Type = itemtypes.UUID

-- Queries PostgreSQL
-- Gets the geodatabase release from the workspace catalog item.

SELECT
  (xpath('//MajorVersion/text()',definition))::text as "Major version",
  (xpath('//MinorVersion/text()',definition))::text as "Minor version",
  (xpath('//BugfixVersion/text()',definition))::text as "Bug fix version"
FROM
 sde.gdb_items AS items INNER JOIN
  (SELECT uuid 
   FROM sde.gdb_itemtypes
   WHERE name = 'Workspace') AS itemtypes
  ON items.type = itemtypes.uuid;

-- Queries Oracle
-- Gets the geodatabase release from the workspace catalog item.

SELECT
  EXTRACTVALUE(XMLType(Definition), '/DEWorkspace/MajorVersion') AS "Major version",
  EXTRACTVALUE(XMLType(Definition), '/DEWorkspace/MinorVersion') AS "Minor version",
  EXTRACTVALUE(XMLType(Definition), '/DEWorkspace/BugfixVersion') AS "Bug fix version"
FROM
 sde.gdb_items_vw items INNER JOIN
  (SELECT UUID 
   FROM sde.gdb_itemtypes
   WHERE Name = 'Workspace') itemtypes
  ON items.Type = itemtypes.UUID;