How to run SQL in terminal
InterSystems FAQ rubric
Too run SQL in Terminal use $system.SQL.Shell().
Please refer to the sample below.
SAMPLES>do $System.SQL.Shell()
SQL Command Line Shell
---------------------------------------------------- The command prefix is currently set to: <>.
Enter q to quit, ? for help.
SAMPLES>>select * from Sample.Vendor
1. select * from Sample.Vendor Vendor Balance Contact DaysClear DiscDays DiscRate LastInvDate LastPayDate MinPayment Name NetDays PayFlag TaxReportingAddress_City Address_State Address_Street Address_Zip
： *If you press Enter without entering anything on the first line, it will switch to multi-line input mode.
USER>d $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
Enter q to quit, ? for help.
USER>> << entering multiline statement mode, 'GO' to execute >>
1>>set transaction isolation level read committed
2>>goFor details, please refer to the document below.
Comments
You can also use the ":sql" alias to launch the SQL Shell :
USER>:sql
SQL Command Line Shell
----------------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter <command>, 'q' to quit, '?' for help.
[SQL]USER>>select sysdate
2. select sysdate
| Expression_1 |
| -- |
| 2025-02-18 13:39:24 |
1 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0928s/35,642/155,490/1ms
execute time(s)/globals/cmds/disk: 0.0001s/3/408/0ms
query class: %sqlcq.USER.cls6
---------------------------------------------------------------------------
[SQL]USER>>quit
USER>And just type ":?" to get the list of all the alias :
USER>:?
:<number> Recall command # <number>
:py Do $system.Python.Shell()
:mdx Do $system.DeepSee.Shell()
:sql Do $system.SQL.Shell()
:tsql Do $system.SQL.TSQLShell()
:alias Create/display aliases
:unalias Remove aliases
:history Display command history
:clear Clear history buffer
:? Display help
Ctrl+R Reverse incremental search
USER>Hi,
is there a way to list all existing schemas defined in the namespace when browsing in SQL Command Line Shell?
USER>:sql
SQL Command Line Shell
----------------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter <command>, 'q' to quit, '?' for help.
[SQL]USER>>browse
Schemas defined in namespace USER
Schema:
check this tool https://pypi.org/project/irissqlcli/
Thanks Dmitry, I will try it as soon I manage to install pendulum.
select * from INFORMATION_SCHEMA.SCHEMATA;
There are many tables in INFORMATION_SCHEMA schema you can query to get info, check:
select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA ='INFORMATION_SCHEMA';
Thanks Enrico