Common SQL queries

Updated   Read time 1 min 24 sec(s)   (2894 views).  Windows  

The following tables list common sql queries for MS-SQL, Postgres, MySQL and Oracle database environments.

MS-SQL

CommandDescription
SELECT @@version DB version
EXEC xp_msver Detailed version info
EXEC master..xp_cmdshell 'net user' Run OS command
SELECT HOST_NAME() Hostname & IP
SELECT DB_NAME() Current DB
SELECT name FROM master..sysdatabases; List DBs
SELECT user_name() Current user
SELECT name FROM master..syslogins List users
SELECT name,password_hash FROM master.sys.sql_logins Password hashes
SELECT name FROM master..sysobjects WHERE xtype='U'; List tables
SELECT name FROM syscolumns WHERE id=(SELECT id FROM sysobjects WHERE name='mytable'); List columns

Postgres

CommandDescription
SELECT version(); DB version
SELECT inet_server_addr(); Hostname & IP
SELECT current_database(); Current DB
SELECT datname FROM pg_database; List DBs
SELECT user; Current user
SELECT username from pg_user; List users
SELECT username,passwd from pg_shadow; List password hashes

MySQL

CommandDescription
SELECT @@version; DB version
SELECT @@hostname; Hostname & IP
SELECT database(); Current DB
SELECT distinct(db) FROM mysql.db; List DBs
SELECT user(); Current user
SELECT user FROM mysql.user; List users
SELECT host,user,password FROM mysql.user; List password hashes

Oracle

CommandDescription
SELECT * FROM v$version; DB version
SELECT version FROM v$instance; DB Version
SELECT instance_name FROM v$instance; Current DB
SELECT name FROM v$database; Current DB
SELECT DISTINCT owner FROM all_tables; List DBs
SELECT user from dual; Current user
SELECT username FROM all_users ORDER BY username; List users
SELECT column_name FROM all_tab_columns; List columns
SELECT table_name FROM all_tables; List tables
SELECT name,password,astatus FROM sys_users; List password hashes

List DBAs

SELECT DISTINCT grantee FROM dba_sys_privs WHERE ADMIN_OPTION = 'YES';