A quick syntax cheat sheet for different databases

Database cheat sheet.md

Current date
Describe
Explain plans
Get type of expression
Run scripts
Show all tables
Show parameters
Truncate table
Run AWRs for Oracle

Current Date

MySQL

SELECT CURDATE();

SELECT NOW();

Postgres

SELECT CURRENT_DATE;

SELECT CURRENT_TIMESTAMP;

Oracle

SELECT SYSDATE FROM DUAL;

SELECT SYSTIMESTAMP FROM DUAL;

SQL Server

SELECT SYSDATETIME();

SELECT CURRENT_TIMESTAMP;

Db2

VALUES(CURRENT DATE);

VALUES(CURRENT TIMESTAMP);

SELECT CURRENT DATE FROM sysibm.sysdummy1;

SELECT CURRENT TIMESTAMP FROM sysibm.sysdummy1;

Describe

MySQL | Oracle

DESCRIBE <table_name>;

Postgres

\d <table_name>;

SQL Server

sp_help <table_name>;

Db2

DESCRIBE TABLE <table_name>;
DESCRIBE SELECT * FROM <table_name>;

Explain plans

MySQL

EXPLAIN [FORMAT = [TRADITIONAL | TREE | JSON]] <SQL>;

EXPLAIN ANALYZE <SQL>;

Postgres

EXPLAIN [ ANALYZE ] [ VERBOSE ] <SQL>;

Oracle

EXPLAIN PLAN FOR <SQL>;

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY (FORMAT=>'ALL +OUTLINE'));

SQL Server

SET SHOWPLAN_TEXT ON;
<SQL>
<SQL>
<SQL>
SET SHOWPLAN_TEXT OFF;

Db2

CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C', 
        CAST (NULL AS VARCHAR(128)), CAST (NULL AS VARCHAR(128)))

EXPLAIN PLAN FOR <SQL>;

db2exfmt -d <DB> -o explain.txt

Snowflake

EXPLAIN USING TABULAR <SQL>;

Get type of expression

Postgres

SELECT pg_typeof(<expression>);

Oracle

SELECT dump(<expression>) FROM DUAL;

Run scripts

MySQL

source <script path/file>

Postgres

\i <script path/file>

Oracle

@<script path/file>

SQL Server

:r <script path/file>

Db2

db2 -tvmf <script path/file>

Show all tables

MySQL

SHOW TABLES;

Postgres

\dt

SELECT * FROM pg_catalog.pg_tables;

Oracle

SELECT * FROM DBA_TABLES;

SQL Server

Db2

Snowflake

Show parameters

MySQL

SHOW VARIABLES [LIKE '<string>'];

Postgres

SHOW [<parameter> | ALL];

Oracle

SHOW PARAMETER <string>

Truncate table

MySQL | Oracle | SQL Server

TRUNCATE TABLE <table_name>;

Db2

TRUNCATE TABLE <table_name> IMMEDIATE;

Run AWRs for Oracle

# Little script to take an AWR snapshot and return the snap id
take_snapshot() {
  sql -s / as sysdba <<EOF
    set pagesize 0 feedback off verify off heading off echo off;
    select DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('ALL') AS a from dual;
    exit;
EOF
}

# Little script to get the dbid
get_dbid() {
  sql -s / as sysdba <<EOF
    set pagesize 0 feedback off verify off heading off echo off;
    select dbid from v\$database;
    exit;
EOF
}

# Generate AWR report (dbid, snap_id_begin, snap_id_end, log_file)
generate_snapshot() {
  sql -s / as sysdba > $4 <<EOF
    set pagesize 0 feedback off termout off;
    SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML($1, 1, $2, $3));
    exit;
EOF
}

# Take snapshot ids
snap_id_begin=$(take_snapshot)
snap_id_end=$(take_snapshot)

dbid=$(get_dbid)
generate_snapshot($dbid, $snap_id_begin, $snap_id_end, "awr.html")