SQL queries — You can enter ad hoc SQL queries that are run against the database and the results displayed. You must terminate the query with a semi-colon and carriage return.

Procedure calls — You can have sqlcmd execute a stored procedure. You identify a procedure call with the exec directive, followed by the procedure class name, the procedure parameters, and a closing semi-colon. For example, the following sqlcmd directive executes the @SystemCatalog system procedure requesting information about the stored procedures.

$ sqlcmd
1> exec @SystemCatalog procedures;

Note that string values can be entered as plain text or enclosed in single quotation marks. Also, the exec directive must be terminated by a semi-colon.

Echo directives — The echo and echoerror directives let you add comments or informational messages to the sqlcmd output. Any text following the directive up to and including the line break or carriage return is repeated verbatim:

Show, Describe, and Explain directives — The show, describe, and explain directives let you examine the structure of the schema and user-defined stored procedures. Valid directives are:

Query statistics directive — The querystats directive lets you select and format the output of the @Statistics system procedure using SQL-like syntax. In the directive you specify a SELECT statement identifying the columns you want returned, using FROM STATISTICS(selector, delta-flag) in place of the table name. You can also use the WHERE, ORDER BY, and GROUP BY clauses to filter the results as desired. For example, the following directive returns the total number of rows in each table:

$ sqlcmd 
1> querystats select table_name, sum(tuple_count) from statistics(table,0) group by table_name;

Class management directives — The load classes and remove classes directives let you add and remove Java classes from the database:

Command recall — You can recall previous commands using the up and down arrow keys. Or you can recall a specific command by line number (the command prompt shows the line number) using the recall command. For example:

$ sqlcmd
1> select * from votes;
2> show procedures;
3> recall 1
select * from votes;

Once recalled, you can edit the command before reissuing it using typical editing keys, such as the left and right arrow keys and backspace and delete.

Script files — You can run multiple queries or stored procedures in a single command using the file directive. The file directive takes one or more text files as an argument and executes all of the SQL queries and exec directives in the file(s) as if they were entered interactively. (Do not use control directives such as recall and exit in script files.) Separate multiple script files with spaces. Enclose file names that contain spaces with single quotation marks. For example, the first command in the following example processes all of the SQL queries and procedure invocations in the file myscript.sql. The second command processes the SQL queries from two files:

$ sqlcmd
1> file myscript.sql;
2> file yourscript.sql 'their script.sql';

If the file(s) contain only data definition language (DDL) statements, you can also have the files processed as a single batch by including the -batch argument:

$ sqlcmd
1> file -batch myscript.sql;

If a file or set of statements includes both DDL and DML statements, you can still batch process a group of DDL statements by enclosing the statements in a file -inlinebatch directive and the specified end marker. For example, in the following code the three CREATE PROCEDURE statements are processed as a batch:

load classes myprocs.jar;
file -inlinebatch END_OF_BATCH
CREATE PROCEDURE FROM CLASS procs.AddEmployee;
CREATE PROCEDURE FROM CLASS procs.ChangeDept;
CREATE PROCEDURE FROM CLASS procs.PromoteEmployee;
END_OF_BATCH

Batch processing the DDL statements has two effects:

Exit — When you are done with your interactive session, enter the exit directive to end the session and return to the shell prompt.