REST API: SQL Tables

Obtain a list of tables associated with the data source. The SQL Tables service implements the CA IDMS Server JDBC API method getTables within the class IdmsDatabaseMetaData.
HTTP Method and URI Path
The URI path has the following format:
GET
<base url>
/sql/tables/
<datasource>
?
<query parameters>
Where:
  • <base URL>
    Specifies the base URL address for the CA IDMS REST API service
  • <datasource>
    (required) Identifies the CA IDMS system where the API request is to be sent and is defined in the datasources.yml file
  • <query parameters>
    (optional) The following parameters can be used to filter results. See the "About Parameters and Parameter Values" for usage information.
    • schemaPattern -- Filter pattern for the schema name
    • tablePattern -- Filter pattern for the table name
    • types -- List of the types of tables to be returned in the response. Valid values for CA IDMS are:
      • TABLE
      • VIEW
By default, the SQL Tables
service returns only tables that are accessible to the current user.
About Parameters and Parameter Values:
  • The parameter "catalog" is not used within CA IDMS.
  • The filtering parameters allow the wildcards
    %
    and
    _
    . Within a pattern string,
    %
    means match any substring of 0 or more characters, and
    _
    means match any one character. Only metadata entries matching the search pattern are returned. In the URL, the
    %
    must be encoded as
    %25
    .
    To see how wildcards can be used in your request, see Examples for Specifying Patterns.
  • Parameter values are case sensitive.
Example: Returns all tables accessible to the user (no filter option used)
https://zoshost.domain.com:10010/api/v1/sql/tables/SYSDEMO
Response:
{ "tables": [ { "tableCatalog": null, "tableSchema": "DEMOEMPL", "tableName": "BENEFITS", "tableType": "TABLE", "tableRemarks": null, "typeCatalog": null, "typeSchema": null, "typeName": null, "referencingColumnName": null, "referencingGeneration": null }, { "tableCatalog": null, "tableSchema": "DEMOEMPL", "tableName": "COVERAGE", "tableType": "TABLE", "tableRemarks": null, "typeCatalog": null, "typeSchema": null, "typeName": null, "referencingColumnName": null, "referencingGeneration": null }, { "tableCatalog": null, "tableSchema": "DEMOEMPL", "tableName": "DEPARTMENT", "tableType": "TABLE", "tableRemarks": null, "typeCatalog": null, "typeSchema": null, "typeName": null, "referencingColumnName": null, "referencingGeneration": null }, { "tableCatalog": null, "tableSchema": "DEMOEMPL", "tableName": "DIVISION", "tableType": "TABLE", "tableRemarks": null, "typeCatalog": null, "typeSchema": null, "typeName": null, "referencingColumnName": null, "referencingGeneration": null }
Example:
Returns all tables accessible to the user using a filter pattern
https://zoshost.domain.com:10010/api/v1/sql/tables/SYSDEMO/?tablePattern=EMP%
Response:
{ "tables": [ { "tableCatalog": null, "tableSchema": "DEMOEMPL", "tableName": "EMPLOYEE", "tableType": "TABLE", "tableRemarks": null, "typeCatalog": null, "typeSchema": null, "typeName": null, "referencingColumnName": null, "referencingGeneration": null }, { "tableCatalog": null, "tableSchema": "NET", "tableName": "EMPLOYEE", "tableType": "TABLE", "tableRemarks": null, "typeCatalog": null, "typeSchema": null, "typeName": null, "referencingColumnName": null, "referencingGeneration": null }, { "tableCatalog": null, "tableSchema": "NET", "tableName": "EMPOSITION", "tableType": "TABLE", "tableRemarks": null, "typeCatalog": null, "typeSchema": null, "typeName": null, "referencingColumnName": null, "referencingGeneration": null } ], "errors": [] }
Example:
Returns all tables accessible to the user using three filtering options
https://zoshost.domain.com:10010/api/v1/sql/tables/SYSDEMO/?schemaPattern=DEMO%&tablePattern=EMP%&types=TABLE
Response:
{ "tables": [ { "tableCatalog": null, "tableSchema": "DEMOEMPL", "tableName": "EMPLOYEE", "tableType": "TABLE", "tableRemarks": null, "typeCatalog": null, "typeSchema": null, "typeName": null, "referencingColumnName": null, "referencingGeneration": null } ], "errors": [] }