---
parser: v2
auto_validation: true
time: 10
author_name: Dan van Leeuwen
author_profile: https://github.com/danielva
tags: [tutorial>beginner, software-product>sap-hana-cloud, software-product-function>sap-hana-cloud--sap-hana-database, software-product-function>sap-hana-cloud--data-lake]
primary_tag: software-product>sap-hana-cloud
slug: hana-cloud-sqltools-console
canonical_url: https://developers.sap.com/tutorials/hana-cloud-sqltools-console
---

# Query Databases Using the SQL Console in SAP HANA Cloud Central

<!-- description --> Learn how the SQL console can be used within SAP HANA Cloud Central to quickly query a selected database.  

## Prerequisites

- You have completed the first 3 tutorials in this group
- You have completed [this](hana-cloud-dl-clients-overview) tutorial which creates a database schema for an SAP HANA Cloud, data lake Relational Engine

## You will learn

- How to open a SQL console, specify the credentials, and set the current schema
- An overview of the functionality provided in the SQL console

---

### Open a SQL console

This step demonstrates how a SQL console can quickly be opened from within SAP HANA Cloud Central and how to change the SQL console's credentials and schema.

1. In **SAP HANA Cloud Central** open a SQL console by selecting **SQL Console** in the left pane.  Notice that the SQL console is not associated with a database when opened in this way.

    ![open SQL console](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/open-sql-console.png)

    Additional SQL consoles can also be opened by selecting the **+** icon.

2. This time select **Instances**, select a database, and choose **Open SQL Console** from the actions menu.

    ![open SQL console from an instance](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/open-sql-console-instance.png)

    Notice that it is now connected to the instance named `HC_HDB` as shown by the name of the tab and the connection label or through the below SQL.

   ```SQL
   SELECT CURRENT_USER FROM DUMMY;
   ```

    ![SQL console connected to HC_HDB](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/sql-console-connected.png)

3. There are several ways to connect to a database as a different user:

    - **From the Instances list:** Click the **three dots** next to the instance and select **Sign in with Different User**. Enter the username and password for the user you wish to connect with.

        ![Change credentials in instances](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/different-user.png)

    - **From the SQL console Connect menu:** Select the **Connected to** link, click the **Connect ...** button, and provide the new username and password.

        ![Change credentials](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/change-credentials.png)

        The Enter Credentials dialog will then ask for the new credentials to be used.

        ![change credentials](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/change-credenitals2.png)

    - **Using SQL:** Run the `CONNECT` statement to switch users within the current console session.

        ```SQL
        CONNECT USER1 PASSWORD Password1;
        SELECT CURRENT_USER FROM DUMMY;
        ```

        ![Change credentials using SQL](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/change-credentials-sql.png)

        The SQL to view the current user for a data lake Relational Engine is shown below.

        ```SQL
        SELECT CURRENT USER;
        ```

        ![Show current user for a data lake Relational Engine](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/current-user-dl.png)

    > The Connect menu and SQL `CONNECT` statement are session-scoped — after a disconnect and reconnect, the console reverts to the credentials it was originally opened with.

4. If you need to alter the credentials for a database user, there are two ways to do this:

    - **Using the SQL console:** Run the following statement, replacing the placeholders with the target username and new password.

        ```SQL
        ALTER USER <user_name> PASSWORD <new_password>;
        ```

    - **Using the SAP HANA Cloud Central UI:** Open the instance, go to the **Overview** tab, and scroll down to **User and Authorization Management**. Select **Users**, click on the user you want to update, and choose **Edit** to make your changes.

    ![Changing user credentials using the UI](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/user-ui.png)

5. The current schema can be set and viewed for a SAP HANA database using the SQL statements below or by clicking on the Current Schema link.

   ```SQL
   SET SCHEMA HOTELS;
   SELECT CURRENT_SCHEMA, CURRENT_USER FROM DUMMY;
   ```

    ![Set the schema](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/set-schema.png)

    For data lake Relational Engine instances, the current schema can be set and viewed as shown below.

   ```SQL
   SET SCHEMA HOTELS;
   SELECT CURRENT SCHEMA, CURRENT USER;
   ```

    ![Set the schema of a data lake Relational Engine](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/set-schema-dl.png)

### Execute SQL

This step demonstrates how to execute a SQL query, examine the statement help, view the query results, and history tab within a SQL console.  

1. Execute the following SQL statements.

   ```SQL
   SELECT * FROM HOTELS.CUSTOMER;
   SELECT * FROM NON_EXISTENT_TABLE;
   SELECT * FROM NON_EXISTENT_TABLE2;
   SELECT * FROM HOTELS.HOTEL_ROOMS_VIEW;
   ```

    The following error dialog appears.  Select  **Skip All**.

    ![SQL execution error dialog](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/error-dialog.png)

    Notice that there is an error marker beside the lines that could not be executed.  Pressing **Alt+E** will display further details and advances to the next error when pressed more than once.

    ![SQL results](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/sql-results.png)

    Notice that two result tabs are shown, one for each SQL statement that generated a result.

2. Results can be downloaded as shown below.

    ![Download results](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/download-results.png)

    Options are provided on how to format the data.

    ![Download options](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/download-options.png)

    Rows can also be selected and then copied to the clipboard by pressing Ctrl+C.

    ![Copy results](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/copy-results.png)

3. Place the cursor on line four and open the **Syntax Help** panel.

    ![Statement help panel](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/statement-help.png)

    Notice that for SAP HANA Cloud, SAP HANA databases, links to the related documentation and details on the objects used in the SQL statement are shown including a link which will open the database objects app where additional details of the object can be viewed.

<!-- FEATURE UNRELEASED


4. Query results can also be displayed as a chart. After executing a query, select the **Visualize Data** tab in the results panel to switch to a graphical view. Execute the following SQL statements to try it out.

   ```SQL
   SELECT TOP 7
       COUNT(*) AS NUM_OF_TABLES, SCHEMA_NAME
   FROM M_TABLES WHERE SCHEMA_NAME != 'SYS'
   GROUP BY SCHEMA_NAME ORDER BY NUM_OF_TABLES DESC;
   ```

    ![Visualize Data](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/visualize-data.png)

    Choose a chart type such as bar, horizontal bar, line, pie, donut, or scatter, and configure the axes to visualize the data.

    ![Data Graphs](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/data-graphs.png)
    
-->
    
5. Commonly used shortcut keys are listed below.  Try a few of them out.

    Action | Shortcut
    ------ | ------
    Add Comment Block | Ctrl+Shift+/
    Comment/Uncomment Line | Ctrl+/
    To Uppercase | Ctrl+U
    To Lowercase | Ctrl+Shift+U
    Go to Next Error | Alt+E
    Go to Previous Error | Alt+Shift+E
    Go to Line | Ctrl+L
    Jump to Matching Brackets | Ctrl+P
    Run All | F8
    Run Statement |	F9
    Text Completion | Ctrl+Space 
    Save to statement library | Ctrl+S
    Move Tab Left | Ctrl+Shift+< (less than sign)
    Move Tab Right | Ctrl+Shift+> (greater than sign)

    >The shortcut keys may vary depending on the OS and browser used.

    For the complete list see [Common Keyboard Shortcuts for the SQL Console](https://help.sap.com/docs/hana-cloud/sap-hana-cloud-administration-guide/keyboard-shortcuts-for-sql-console).

6. Examine the **History** tab.

    ![History tab](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/messages-history-tab.png)

    Notice details for each executed statement are displayed, including metrics such as memory consumption. Statements can be quickly located using the search functionality. Selected statements may be inserted back into the SQL console or copied for further use.

    The columns can also be changed by selecting the gear icon in the right corner.

    ![History tab columns](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/history-tab-columns.png)

7. Examine the **SQL Console Settings**.  

    ![Connection settings](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/connection-settings.png)

    - Execute the following SQL which is used to illustrate the result behavior settings.

        ```SQL
        SELECT * FROM M_SYSTEM_INFORMATION_STATEMENTS;

        SELECT COUNT(*) FROM SYS.TABLE_COLUMNS;
        SELECT * FROM TABLE_COLUMNS;
        ```

        Double click on a cell to open the viewer. Notice that only the first 1024 bytes from the column STATEMENT are displayed in the results view for the Blocked Transactions row.

        ![Limit for large objects](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/settings-result3.png)

        Notice that over 6000 rows are in TABLE_COLUMNS.

        ![One thousand row limit](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/settings-result1.png)

        The first 1000 are displayed.

        ![One thousand row limit](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/settings-result2.png)

        These limits can be adjusted in the result behavior settings shown below.

        ![Result behavior settings](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/result-behavior.png)

8. Execute the following SQL which is used to illustrate the result display settings.

    ```SQL
    SELECT CURRENT_DATE, CURRENT_TIMESTAMP(7), RAND() * 10 FROM DUMMY;
    ```

    The below is the result when this setting is disabled.

    ![No formatting](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/no-formatting.png)

    Enable Result Format Settings in the settings.

    ![Format settings](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/result-format-settings.png)

    The below is the result when this setting is enabled.

    ![Result formatted](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/formatted.png)

9. The overall theme for SAP HANA Cloud Central can be set in the settings which also affects the theme of the SQL console.

    ![Theme](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/theme.png)

10. Execute the following SQL statements.

   ```SQL
   SELECT HEXTOBIN ('48656C6C6F20776F726C64') BINARY_EXAMPLE FROM DUMMY;

   SELECT '{
       "name":"John",
       "age":30,
       "cars": {
           "car1":"Ford",
           "car2":"BMW",
           "car3":"Fiat"
       }
       }' AS JSON_EXAMPLE
       FROM DUMMY;

   SELECT '<?xml version="1.0" encoding="UTF-8"?>
       <breakfast_menu>
       <food>
           <name>Strawberry Belgian Waffles</name>
           <price>$7.95</price>
           <description>
           Light Belgian waffles covered with strawberries and whipped cream
           </description>
           <calories>900</calories>
       </food>
       <food>
           <name>French Toast</name>
           <price>$4.50</price>
           <description>
           Thick slices made from our homemade sourdough bread
           </description>
           <calories>600</calories>
       </food>
       <food>
           <name>Homestyle Breakfast</name>
           <price>$6.95</price>
           <description>
           Two eggs, bacon or sausage, toast, and our ever-popular hash browns
           </description>
           <calories>950</calories>
       </food>
       </breakfast_menu>' XML_EXAMPLE FROM DUMMY
   ```

   Double tapping on a result will open a result viewer.

   ![SQL results](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/result-viewer.png)

11. The Auto-save contents of SQL consoles setting can be used to persist the open SQL console tabs and their contents. This setting is on by default.

    ![Auto save](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/auto-save.png)

12. SQL statements can also be executed in the background. This allows users to start a long-running statement, disconnect, and return later to view the results.

    Execute the following statement by clicking the Run dropdown menu and selecting Run Statement in Background:

   ```SQL
   DO BEGIN
   --Wait for a few SECONDS
       USING SQLSCRIPT_SYNC AS SYNCLIB;
       CALL SYNCLIB:SLEEP_SECONDS(3); --runs for longer than 3 SECONDS
       --Now execute a query
       SELECT * FROM M_TABLES;
   END
   ```

   ![RunBackgroundStatement](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/RunBackgroundStatement.png)

   The results will appear in the Background Statements tab at the bottom on the console.

13. The actions menu for a SQL console tab offers additional options such as the ability to rename a tab, to move a tab, to open a previously closed tab, or to search through the content of all open SQL console tabs.

    ![SQL console actions menu](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/actions-menu.png)

    ![Search through tabs](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/search-through-tabs.png)

### Statement library

The statement library is a convenient location in the SQL console to store and retrieve frequently executed SQL statements.  It provides a place to store statements that are used frequently as to not type them in repeatedly.

The library is pre-populated with useful statements called ‘SYSTEM’ statements.

![Statement library system statements](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/statement_libaray_system.png)

You may also define custom statements that are only available to you. These are ‘USER’ statements.

```SQL
/*

[DESCRIPTION]

- Future guest check-ins across all hotels

*/

SELECT * FROM RESERVATION
    WHERE ARRIVAL >= CURRENT_DATE;
```

1. Save your custom statement, add a name and an optional description.

    ![Create user statement](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/save_custom_statement.png)

    Set the name to `All Future Check-ins`.

    ![Save statement](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/save_statement.png)

2. View your custom statement in the statement library

    ![View user statements](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/statement_libaray_user.png)

3. To run a statement, select one from the statement library and click the Run button.  

    ![Run saved statement](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/run_saved_statement.png)

    If you select multiple saved statements, you additionally have the option to open them together in one tab or in individual tabs.

    > It is also possible to export and import SQL statements directly to/from the file system
    > 
    > ![Import or export statements](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/export_import_statements.png) 
    > 
    > Files can then be shared using a version control system such as git.

    User-defined statements can be edited. From the Statement library, select the desired statement, click open, and make the desired changes. Select Save to overwrite the previous statement.

    ![Modify saved statements](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/replace_statement.png)

### GenAI in the SQL console

The SQL console also provides GenAI tools that assist with creating, understanding, and optimizing SQL statements. To enable GenAI in the SQL console, refer to the following [Using Gen AI in SAP HANA Cloud Central](https://help.sap.com/docs/hana-cloud/sap-hana-cloud-administration-guide/using-joule-in-sap-hana-cloud-central?locale=en-US#loio3ec50257efcf49adacebd37babb7455c__section_kpw_4gb_jdc).

1. By selecting SQL text and clicking **Generate SQL From Natural Language** from the Run dropdown menu, users can generate SQL statements from natural-language instructions, and also provides an explanation. Execute the following prompt.

   ```
   List all the column store tables and their columns in the schema HOTELS
   ```

    ![Run prompt](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/RunPrompt.png)

2. To understand an existing query, select the SQL statement and click Explain SQL Statement using AI from the same menu. This returns a clear natural-language explanation of what the query does.

   ```SQL
   SELECT
       t.SCHEMA_NAME,
       t.TABLE_NAME,
       c.COLUMN_NAME
   FROM
       SYS.TABLES AS t
   JOIN
       SYS.TABLE_COLUMNS AS c
       ON t.SCHEMA_NAME = c.SCHEMA_NAME
       AND t.TABLE_NAME = c.TABLE_NAME
   ORDER BY
       t.SCHEMA_NAME,
       t.TABLE_NAME,
       c.POSITION;
   ```

    ![Explain AI](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/ExplainAI.png)

3. To improve a query, select the SQL statement and click Optimize SQL Statement using AI. The system will suggest a refined version of the query, often improving readability and performance.

   ```SQL
   SELECT 
       TABLES.SCHEMA_NAME,
       TABLES.TABLE_NAME,
       TABLE_COLUMNS.COLUMN_NAME
   FROM 
       TABLE_COLUMNS
       INNER JOIN
       TABLES
       ON TABLE_COLUMNS.TABLE_NAME = TABLES.TABLE_NAME;
   ```

    ![Optimize AI](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/OptimizeAI.png)

### Knowledge check

Congratulations! You have now used the SQL console in SAP HANA Cloud Central and have become familiar with some of the features it provides.