Common SQL queries
Updated
Read time 1 min 24 sec(s) (3036 views).
Windows
The following tables list common sql queries for MS-SQL, Postgres, MySQL and Oracle database environments.
MS-SQL
Command | Description |
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
Command | Description |
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
Command | Description |
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
Command | Description |
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';