Determine the Database Version in SQL

1. Overview

All relational databases support determining the database server version using an SQL query. If we cannot run an SQL query against the database server, we can determine the version by other means such as the software used to install the database or connection information.

In this tutorial, we’ll learn about the options for determining the database version in SQL databases. We don’t need any special schema setup for this tutorial and only need a database installation whose version is to be determined.

2. Determining MySQL Version

To find the MySQL server version, let’s connect to the server using the mysql client as the root user, which doesn’t require a password by default:

$ mysql -u root

2.1. Using Connection Information

When we connect to the MySQL server using the mysql client, the database version is displayed at the very beginning:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.39 MySQL Community Server - GPL

2.2. Using an SQL Function

Having connected to the MySQL server, we can use the VERSION() information function that returns a string that indicates the MySQL server version:

myssql>SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.39    |
+-----------+

Furthermore, we don’t need to launch a shell to run the SQL query. We can use the –execute, or -e, option to the mysql client to run a single SQL statement and exit:

$ mysql -u root -e "SELECT VERSION()"
+-----------+
| VERSION() |
+-----------+
| 8.0.39    |
+-----------+

2.3. Using System Variable

Alternatively, we can find the database server version from the system variable called version:

SELECT @@GLOBAL.version;
+------------------+
| @@GLOBAL.version |
+------------------+
| 8.0.39           |
+------------------+

2.4. Using Software Information

The software binaries used to install MySQL database include the database version, for example, the MSI Installer mysql-8.4.4-winx64.msi, ZIP Archive mysql-8.4.4-winx64.zip, and the Docker image tag mysql:8.4.4. A MySQL database installation includes the server version in the directory path as well, for example C:\Program Files\MySQL\MySQL Server 8.0.33.

3. Determining PostgreSQL Version

To find the PostgreSQL server version, let’s connect to the server using the psql client as the postgres user, whose password we know:

$ psql -U postgres
Password for user postgres:

3.1. Using an SQL Function

After connecting to the PostgreSQL server, we can use the version() information function to find the server version:

postgres=# SELECT version();
                                 version
-------------------------------------------------------------------------
 PostgreSQL 17.2 on x86_64-windows, compiled by msvc-19.42.34435, 64-bit

When we don’t have a Postgres shell started, we can still run an SQL query to call the version() function using the psql client:

$ psql -c "SELECT version();"

3.2. Using Server Configuration

Alternatively, we can find the PostgreSQL database server version from the server configuration preset option called server_version:

postgres=# show server_version;
 server_version
----------------
 17.2

Furthermore, we can use the pg_config utility to determine the server version from the configuration parameter called VERSION:

SELECT * 
FROM pg_config
WHERE name = 'VERSION';
  name   |     setting
---------+-----------------
 VERSION | PostgreSQL 17.2

3.3. Using Software Information

The software binaries used to install PostgreSQL database include the database version, for example, the Mac OS X binaries postgresql-17.2-1-osx.dmg, Windows binaries postgresql-17.2-3-windows-x64, and the Docker image tag postgres:17.2. A PostgreSQL database installation includes the server version in the directory path as well, for example C:\Program Files\PostgreSQL\17.

4. Determining SQL Server Version

Let’s use the SQL Server Management Studio (SSMS) to connect to a running SQL Server. We can alternatively use the command-line client mssql-cli; however, the graphical user interface SMSS has more options to determine the server version.

4.1. Using Connection Information

When we connect to the SQL Server server using the SMSS management tool, the server version is displayed in the connection string:
SQL Server 16.0.4155.4 is listed in the connection.

4.2. Using an SQL Function

After connecting to the SQL server, we can use the Transact SQL configuration function @@VERSION to find the server version:

SELECT @@VERSION;
Microsoft SQL Server 2022 (RTM-CU15-GDR) (KB5046862) - 16.0.4155.4 (X64) Web Edition (64-bit) on Windows Server 2022 Datacenter 10.0  (Build 20348: ) (Hypervisor)

Furthermore, we don’t need to start a shell to run the SQL query and can use the sqlcmd utility to run a Transact-SQL statement:

sqlcmd -S localhost -U sa -Q 'select @@VERSION'

4.3. Using Server Property

Having connected to the SQL Server, we can find the server version from the server property called productversion:

SELECT SERVERPROPERTY('productversion') AS productversion, SERVERPROPERTY ('productlevel')
  AS productlevel, SERVERPROPERTY ('edition') AS edition;

productversion	productlevel	edition
16.0.4155.4	RTM	Web Edition (64-bit)

4.4. Using SQL Discovery Report

We can run the SQL Server features discovery report to determine the product version. To run the discovery report, we start the SQL Server Installation center. Subsequently, we select Tools in the left margin, followed by selecting Installed SQL Server features discovery report. The SqlDiscoveryReport generated includes the server version, for example, 16.0.4155.4.

4.5. Using Software Information

The software binaries used to install SQL Server database include the database version, for example, the Ubuntu package 16.0.4175.1-3. We can determine the server version by running a Docker image and including the environment variable PAL_PROGRAM_INFO:

docker run -e PAL_PROGRAM_INFO=1 --name sqlversion
  -it mcr.microsoft.com/mssql/server:2022-latest &&
  docker rm sqlversion

5. Conclusion

In this article, we learned about determining database server versions for relational databases. We’ve several options for finding the version information, including using SQL functions for this purpose, system configuration information, connection information, and software-tagged information. Furthermore, we don’t need to launch an interactive shell to run a single SQL statement to determine the version, as most databases have client options to run a single SQL statement and exit.