InterSystems SQL Cheat Sheet

Hi developers!

As you know InterSystems IRIS besides globals, object, document and XML data-models also support relational where SQL is expected as a language to deal with the data.

And as in other relational DBMS InterSystems IRIS has its own dialect.

I start this post to support an SQL cheatsheet and invite you to share your favorites - I'll update the content upon incoming comments.

Here we go!

List all the tables in database:

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

Credit to @Dmitry Maslennikov 

Create table with current date and current time as defaults:

CREATE TABLE CUSTOMER

(

    ID INTEGER PRIMARY KEY NOT NULL,

    NAME VARCHAR(100) NOT NULL,

    PASSWORD VARCHAR(20) NOT NULL,

    LAST_UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,

    REGISTRATION_DATE DATE DEFAULT CURRENT_DATE NOT NULL
);

notice functions CURRENT_TIMESTAMP and CURRENT_DATE are without parenthesis.

Create stored procedure/function that uses ObjectScript inside:

CREATE OR REPLACE FUNCTION IRIS_VERSION()

 RETURNS VARCHAR
 LANGUAGE OBJECTSCRIPT

 {

  Return $ZV

 }

Call Stored procedure/function:

select IRIS_VERSION() as "Version"

Call internal functions.

Return IRIS Version:

Select $version

Return current namespace:

Select $namespace

Run SQL from file (credit @Raj Singh ):

  DO $SYSTEM.SQL.Schema.ImportDDL("c:\InterSystems\mysqlcode.txt",,"IRIS")

Run SQL from file using python terminal(credit @Dmitry Maslennikov): 

irissqlcli iris://_SYSTEM:SYS@localhost:1972/USER < c:\InterSystems\mysqlcode.txt

Open SQL shell in terminal(credit @Chad Severtson):

USER>:sql

Open SQL shell in webterminal (credit @Nikita Savchenko ):

SAMPLES > /sql
SAMPLES:SQL > select top 5 * from Cinema.Film

SAMPLES: SQL > /sql
SAMPLES > write "COS!"
cos!

Add yours?