.::: Check Hostname, IP & Version for MSSQL, MariaDB, MySQL, OracleDB, PostgreSQL from SQL Server LinkedServer :::.

1. MSSQL
select @@servername as ServerName,@@VERSION as VersionSQL from sys.databases;
select @@servername as ServerName,name,log_reuse_wait_desc, recovery_model_desc,compatibility_level,@@VERSION as VersionSQL from sys.databases
 

 

---Open SSMS & Run Command to check version (Capture)
select @@servername as ServerName, @@version as VersionSQL;

-- select IP
SELECT  @@servername as ServerName,
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address
   
---check active transcation
select @@servername as ServerName, @@version as VersionSQL,name,compatibility_level,log_reuse_wait_desc, recovery_model_desc from sys.databases;
 

2. MySQL MariaDB

MariaDB [(none)]> select @@hostname as Hostname, @@version as Version;
+----------+-----------------+
| Hostname | Version         |
+----------+-----------------+
| teguhth  | 10.5.20-MariaDB |
+----------+-----------------+
1 row in set (0.000 sec)

MariaDB [(none)]>

3. Oracle Database
SELECT host_name , version FROM V$INSTANCE;
select banner from v$version;
 

4. Postgresql
SELECT version();
select pg_read_file('/etc/hostname') as hostname, version();
SELECT datname FROM pg_database;

 

5. Check Version/ Test from Linked Server
--from MSSQL

select @@servername as LocalServer, * from openquery(mssql,'select @@servername as LinkedServer');
select @@servername as LocalServer,@@version as VersionSQL, * from openquery(mssql,'select @@servername as LinkedServer,@@version as VersionSQL');
 

--for MariaDB MySQL
select @@servername as LocalServer, * from openquery(mariadb,'select @@hostname as LinkedServer');
select @@servername as LocalServer,@@version as VersionSQL, * from openquery(mariadb,'select @@hostname as LinkedServer, @@version as Version');
 

--from Oracle
select @@servername as LocalServer, * from openquery(oracle,'SELECT host_name as LinkedServer FROM V$INSTANCE');
select @@servername as LocalServer,@@version as VersionSQL, * from openquery(oracle,'SELECT host_name as LinkedServer, version FROM V$INSTANCE;');
select @@servername as LocalServer,@@version as VersionSQL, * from openquery(oracle,'select banner from v$version;');
 

--from postgresql
select @@servername as LocalServer, * from openquery(postgresql,'SELECT datname as DBPostgreSQL FROM pg_database;');
select @@servername as LocalServer,@@version as VersionSQL, * from openquery(postgresql,'SELECT datname as DBPostgreSQL FROM pg_database;');