Add All Complete Objects, Joins and Metadata v2021.2+

To automatically add all complete data objects, joins and column metadata in a single operation, use the Add All Complete Objects, Joins and Metadata feature.

This feature adds new tables, views, stored procedures or database functions that have at least one primary key defined in the data source (called a complete object). Joins are added when there is a foreign key from one complete object to another.

Any data objects or joins that already exist in the configuration will not be updated with information discovered with this method, they are skipped instead. To add missing metadata or join information, use the Discover Database Metadata method described below.

To do so:

1. Right-click on a Data Source.
2. Click Add All Complete Objects, Joins and Metadata in the context menu
3. Click Okay on the confirmation dialog, or Cancel to abort the process.

   

4. Wait for the summary tab to open.

   

   The summary tab shows the result of the operation:
   • Total complete objects added — number of complete data objects added to the configuration
   • Total incomplete objects found — number of incomplete objects found. Incomplete objects are not added to the configuration with this feature. Use Discover Database Metadata to add them.
   • Total objects skipped — number of complete data objects found that already exist, or otherwise not added to the configuration
   • Total joins added — number of joins added to the configuration
   • Total joins skipped — number of joins found that already exist, or otherwise not added to the configuration
   • Total metadata added — number of data object columns that had column metadata added
5. If in step 4 there were any incomplete objects found, either use Discover Database Metadata or manually add the metadata to make them complete.

Schema Discovery

Exago can retrieve primary and foreign key contraint information from the Data Sources by either:

• Executing a SQL queries to discover the schema
• Using ADO.NET GetSchema() calls (default) v2021.2+

SQL Queries

Customizing Data Discovery SQL v2016.3.6+

The SQL statements used for Automatic Database Discovery can be customized if necessary, in order to accommodate non-standard key names.

To customize the discovery SQL:

1. Refer to Managing the dbconfigs.json File for the location of the file to edit. Follow the instructions in the Overriding dbconfigs Information section to correctly override the default behavior depending on your version of Exago.
2. Locate the property for your data source type and edit the SQL for either or both primary and foreign keys.
3. Save the file, then run Database Discovery to see your changes.

"mysql": {
     "PrimaryKeySql": "SELECT CONSTRAINT_NAME AS indexname, TABLE_SCHEMA as schemaname, TABLE_NAME as tablename, COLUMN_NAME as columnname FROM information_schema.key_column_usage WHERE table_schema = schema() AND CONSTRAINT_NAME = 'PRIMARY'",
     "ForeignKeySql": "SELECT TABLE_SCHEMA As schemaname, TABLE_NAME as tablename, COLUMN_NAME as columnname, REFERENCED_TABLE_SCHEMA as referencedschemaname, REFERENCED_TABLE_NAME as referencedtablename, REFERENCED_COLUMN_NAME as referencedcolumnname FROM information_schema.key_column_usage WHERE  table_schema = schema() AND CONSTRAINT_NAME Like 'FK%'"
} 

{
 "mysql": {
  "PrimaryKeysCollection": {
   "CollectionName": "Indexes",
   "ConstraintNameColumn": "INDEX_NAME",
   "SchemaColumn": "INDEX_SCHEMA",
   "TableColumn": "TABLE_NAME",
   "IsPrimaryColumn": "PRIMARY"
  },
  "PrimaryKeyColumnsCollection": {
   "CollectionName": "IndexColumns",
   "ConstraintNameColumn": "INDEX_NAME",
   "SchemaColumn": "INDEX_SCHEMA",
   "TableColumn": "TABLE_NAME",
   "ColumnColumn": "COLUMN_NAME"
  },
  "ForeignKeysCollection": {
   "CollectionName": "Foreign Key Columns",
   "FromSchemaColumn": "TABLE_SCHEMA",
   "FromTableColumn": "TABLE_NAME",
   "FromColumnColumn": "COLUMN_NAME",
   "ToSchemaColumn": "REFERENCED_TABLE_SCHEMA",
   "ToTableColumn": "REFERENCED_TABLE_NAME",
   "ToColumnColumn": "REFERENCED_COLUMN_NAME"
  }
 }
}