Skip to content

Instantly share code, notes, and snippets.

@gvenzl
Last active December 15, 2022 20:43
Show Gist options
  • Save gvenzl/1b6ad9d1958f4fe801a22bc48dcd6199 to your computer and use it in GitHub Desktop.
Save gvenzl/1b6ad9d1958f4fe801a22bc48dcd6199 to your computer and use it in GitHub Desktop.
A quick syntax cheat sheet for different databases

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")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment