SQL Tool

Learn to use Aurea CRM's SQL Tool to carry out changes to the SQL database.

Note: The SQL Tool can only be started by the administrator (SU) and reps assigned a role where the "Aurea CRM SQL Tool" CRM process is not locked, see CRM Process Configuration.You can irretrievably delete your Aurea CRM database and your license with this tool! Only use this program if you have detailed knowledge of the system.
  1. Start the SQL tool by executing mmba.exe (in the .. \system\exe directory) using the following parameter:

    mmba.exe -q

  2. Log on as "SU", as otherwise you cannot access the tool.

    The update.CRM SQL-Tool dialog box is displayed.



  3. In order to enable the functions that carry out far-reaching changes to the database, enter the password "130369" in the Pwd field and click on Enable.
  4. Carry out the desired changes to the database.
    • Create All Tables/Indexes: Creates all tables and indices anew.
    • Drop All Tables/Indexes: Deletes all tables (and all data in the tables) and indices.
      Note: Warning: Your data is deleted irretrievably!
    • Create All Indexes: Creates all indices anew.
    • Drop All Indexes: Deletes all indices.
    • Create Table/Index: Creates the selected table together with its index.
    • Drop Table/Index: Deletes the selected table including all records and indices.
    • Create Index: Creates a new index for the selected table.

      Aurea recommends only changing indices in the Maintenance module, see Data Model.

    • Drop Index: Deletes the selected table's index.
      Note: You need to carry out the "drop" action before carrying out the corresponding "create" action.
    • Rebuild Table: Rebuilds the keys in the selected table.

      Aurea recommends using the Rebuild module to rebuild tables, see Rebuild module.

    • Rebuild All Tables: Rebuilds the keys in all tables.
    • Rebuild COLLATION Indexes, Rebuild NLS_SORT Indexes: Rebuilds the indices used to sort the selected table, see Multi-language Sort (Oracle) and Multi-language Sort (SQL).
    • Rebuild ALL COLLATION Indexes, Rebuild ALL NLS_SORT Indexes: Creates all the indices used to sort data in tables, see Multi-language Sort (Oracle) and Multi-language Sort (SQL).
    • Calculate rep reference: Re-creates rep reference records for the selected info area, see Depict Rep Reference.
    • Count Table: Counts the number of records in the selected table.
    • Count All Tables: Counts the number of records in all tables.
    • Count All Sel. Tables: Counts the number of records in all selection tables.
    • Recreate All Sel. Indexes: Rebuilds the indices in all selection tables.
    • Check Table: Checks whether a table is present in the database.
    • Check All Tables: Checks whether all tables are present in the database.
    • Recreate Sequence: Regenerates the automatically generated serial numbers.
    • Recreate All Sequences: Regenerates all the automatically generated serial numbers.
    • Analyze Table: Synchronizes the memory usage counter (sysindex tables).
    • Analyze All Tables: Carries out an analysis of all tables.
    • Recreate Procedure: Creates the assigned stored procedures anew.
    • Recreate All Procedures: Creates all stored procedures anew.
    • Recreate All Functions: Creates all functions for conditions that affect system fields anew.
    • Rebuild Pers.Counter: Aurea recommends using the Rebuild module, see Rebuild module.
    • Write Script to File: Writes all the executed SQL statements to file under .. \system\db\<table prefix>_ct.sql.
    • Check -> Check Cols.: If this option is enabled, after adding or checking a table, the table is checked that all columns are present.
  5. Click on Done to close the SQL Tool.