All Products
Search
Document Center

Data query

Last Updated: May 09, 2020

Background

You need to use SQL query statements to verify business code, analyze product effect, and locate issues in the production environment.

Important notes

  • You must have permissions to access the target databases and tables.
  • If a table contains sensitive and confidential fields which you have no permission to access, you cannot include such fields in the WHERE clause of SQL statements to query data from the table.
  • When you query a table that contains sensitive and confidential fields which you have no permission to access, such fields are displayed in the ****** format in the query result.
  • You can search for tables or objects on the left of the SQLConsole tab. The search result can contain a maximum of 1,000 records and displays related information such as schemas, fields, and indexes.
  • You can view the search result returned by an SQL statement at the bottom of the SQLConsole tab. By default, a search result can contain a maximum of 200 records. To modify this default setting, go to the Security Rules page by choosing System Management > Security > Security Rules in the top navigation bar. Note that this setting can be modified only by DMS administrators.
  • By default, you can only search for data in a table whose size does not exceed 10 GB. To modify this default setting, go to the Security Rules page by choosing System Management > Security > Security Rules in the top navigation bar. Note that this setting can be modified only by DMS administrators.
  • By default, you can run an SQL statement in a database for a maximum of 60 seconds. To modify this default setting, go to the Security Rules page by choosing System Management > Security > Security Rules in the top navigation bar. Note that this setting can be modified only by DMS administrators.

Go to the Single Database query page

  1. Log on to the DMS console.
  2. In the top navigation bar, choose SQLConsole > Single Database query. In the dialog box that appears, select a database as prompted and click Confirm.
  3. In the SQL editor, enter SQL statements and click Execute.

Description of the Single Database query page

  • Permissions

    In the Permissions section on the top of the SQL editor, you can view the permissions you have on the current database. You can also move the pointer over the Info icon in this section to view the information about the database, such as the database owners and database administrator (DBA).

  • Table Details/Query

    You can click Table Details on the top of the SQL editor to view the list of tables in the current database. To go back to the SQL editor, click Query on the top of the table list.

  • Sync Metadata

    You can click Sync Metadata on the top of the SQL editor to collect the latest metadata information about the current database, such as the tables, fields, indexes, and programmable objects. Based on the collected metadata information, you can manage permissions on the tables, fields, and programmable objects by risk level.

  • Export

    You can move the pointer over Export and select the corresponding menu item to export schemas or table creation statements of the current database. Note that you can export schemas in Word, Excel, or PDF.

  • SQLConsole You can enter SQL statements in the SQL editor to manage the current database. The SQL editor provides some features to help you edit SQL statements, such as Format, Execute Plan, Saved SQL, and SQL Diagnostics.

    • Format: After you click Format, DMS properly formats the SQL statements.
    • Execute Plan: After you click Execute Plan, DMS displays the execution plan of the SQL statements.
    • Saved SQL: You can click Saved SQL to save, manage, or use SQL statements.
    • Character Set: You can click Display Settings and select a character set for displaying query results.
    • SQL Prompt: You can click Display Settings and enable or disable SQL prompts. If you enable this feature, DMS automatically completes table names, field names, and database keywords when you write SQL statements.
    • Show Results: You can click Display Settings and specify whether to overwrite the last execution result after you run SQL statements.
  • Execution results

    After you run SQL statements, execution results appear below the SQL editor. You can view details of each record in the execution result, generate charts, or export the execution result.

  • Execution History

    On the Execution History tab below the SQL editor, you can view the execution history of the SQL statements, including the time when the SQL statements were run, the database where the SQL statements were run, and the SQL statements that were run. You can also export the execution history.