Do you need to know what RDBMS Server version you’re on, and you only have SQL at your disposal? No problem. Most RDBMS provide you with that information in some form of meta data table.
Here’s how:
-- ClickHouse
select version();
-- CockroachDB
select version();
-- Db2
select service_level from table (sysproc.env_get_inst_info()) t
-- Derby
select getdatabaseproductversion() from (values (1)) t (a);
-- DuckDB
select version();
-- Exasol
select param_value
from exa_metadata
where param_name = 'databaseProductVersion';
-- Firebird
select rdb$get_context('SYSTEM', 'ENGINE_VERSION')
from rdb$database;
-- H2
select h2version();
-- HANA
select * from m_database;
-- HSQLDB
select character_value
from information_schema.sql_implementation_info
where implementation_info_name = 'DBMS VERSION'
-- Informix
select dbinfo('version', 'full') from systables where tabid = 1;
-- MariaDB
select version();
-- MemSQL (SingleStore)
select @@memsql_version;
select version(); -- Works, but produces an older MySQL specific version
-- MySQL
select version();
-- Oracle
select * from v$version;
-- PostgreSQL
select version();
-- Snowflake
select current_version();
-- SQL Server
select @@version;
-- SQLite
select sqlite_version();
-- Teradata
select infodata from dbc.dbcinfov where infokey = 'VERSION'
-- Trino
select version();
Missing this info for your own RDBMS? Feel free to comment.
I was expecting the usual punchline of “of course, you can use jOOQ to get the version in a DB-independent way, just call dsl.dbVersion()”.
I had thought about it, but there’s already JDBC’s
DatabaseMetaData.getDatabaseProductVersion()and related API, so is that really of value?Snowflake:
SELECT CURRENT_VERSION();Thanks a lot, will add it to the list
when use DB2 version 9, it works for me: select service_level from sysibmadm.env_inst_info;