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:

CREATETABLE CUSTOMER

(

    IDINTEGER PRIMARY KEYNOTNULL,

    NAMEVARCHAR(100) NOTNULL,

    PASSWORDVARCHAR(20) NOTNULL,

    LAST_UPDATED TIMESTAMPDEFAULTCURRENT_TIMESTAMPNOTNULL,

    REGISTRATION_DATE DATEDEFAULTCURRENT_DATENOTNULL
);

notice functions CURRENT_TIMESTAMP and CURRENT_DATE are without parenthesis.

Create stored procedure/function that uses ObjectScript inside:

CREATEORREPLACEFUNCTION IRIS_VERSION()

 RETURNSVARCHAR
 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?