Get Table Names
Description:
The Get table names step belongs to the Input plugin in Process Studio workflows. The step reads the list of objects existing in a database - catalogs, schemas, tables, views, procedures, and synonyms — and returns the list as output rows.
Use Get table names to inventory a database or to feed object names into later steps in the workflow.
The step normally starts a workflow and doesn't require incoming rows. Incoming rows are read only when Get schema from field is selected.
The step ignores any error it hits while reading views. When the database refuses the request, the step returns no view rows and writes nothing to the log, so an empty result can look like a database with no views.
Example:
List every table in a Postgres database
The following configuration reads all table names available to a Postgres connection, prefixes each name with the schema, and returns the CREATE statement alongside each name.
Connection and schema:
- Step name: Get table names
- Shared Connection: Local_Postgres_dvdrental
- Schema name: empty
- Get schema from field: cleared
- Schema name field: unavailable
Settings:
- Include tables: selected
- Add schema in object name: selected
- All other Include options: cleared
Output fields
- Tablename fieldname: tablename
- Object type fieldname: type
- Is system object fieldname: is system
- Creation SQL fieldname: sql data
Sample output
The step returns one row per table. The first rows resemble the following:
Figure: Sample output rows returned by the Get table names step.
Configurations:
| Field Name | Description |
|---|---|
| Step Name | Specify a unique name for the step, which will help you identify and reference it easily when debugging or linking steps in the workflow. The field is mandatory. |
| Connection | Select the connection to the database whose object names the step reads. The field is mandatory. The label changes to Local Connection or Shared Connection after you pick a connection. |
| Schema Name | Specify a schema name to limit the results to a single schema. Leave the field empty to list every object accessible to the connection. The field accepts a static value or a variable. Note: After a schema name is entered, Process Studio clears Include catalogs and makes the checkbox unavailable, because a catalog listing and a schema filter can't be combined. The field accepts static or variable value. |
| Get Schema from field | Select the checkbox when the schema name isn't fixed and instead comes from a field in the previous step. Note: Once the checkbox is selected: • Make Schema name field available. • Make Schema name unavailable. • Clear Include catalogs and make the checkbox unavailable. Make Preview unavailable, because no data exists until the workflow runs. |
| Schema Name Field | Select the incoming field holding the schema name. The list shows the fields from the previous step. The field becomes available only after Get schema from field is selected. |
| Settings: | Select the types of database objects to include in the output. At least one type must be selected. Otherwise, the step stops and displays the message "You must specify at least one type!" The step writes the message to the log when the workflow runs, and does not start. |
| Include catalogs | Select the checkbox to list the catalogs (databases) available to the connection. The checkbox is unavailable when a schema name is entered or when Get schema from field is selected. Cleared by default. |
| Include schemas | Select the checkbox to see available schemas from the database. Default value: Cleared. |
| Include tables | Select the checkbox to see available tables from the database. Default value: Selected. |
| Include views | Select the checkbox to see type views tables from the database. Default value: Selected. |
| Include procedures | Select the checkbox to see type procedures tables from the database. Default value: Selected. |
| Include synonyms | Select the checkbox to see type synonyms tables from the database. Default value: Selected. |
| Add schema in object name | Select the checkbox to add the schema name as a prefix to the value in the Table name fieldname output field. For example, the output shows dbo.CUSTOMER instead of CUSTOMER. Default value: Cleared. |
| Output Fields: | |
| Tablename fieldname | Specify the name of the output field holding the object name. The field is required. When the field is empty, the dialog displays error : "Tablename field is missing!" Default Value: tablename |
| Object type fieldname | Specify the name of the output field holding the object type: CATALOG, SCHEMA, TABLE, VIEW, PROCEDURE, or SYNONYM. Default Value: type |
| Is system object fieldname | Specify the name of the output field indicating whether the object belongs to the database system. The step adds the field as a Boolean, so the value reads Y or N. Default Value: is system |
| Creation SQL fieldname | Specify the name of the output field holding the CREATE statement for the object. Note: The field returns value only if type is TABLE. |
| Preview | Select Preview to run the step alone and confirm the settings before running the whole workflow. Process Studio prompts for the number of rows to display and then shows the rows. Note: Preview is unavailable when Get schema from field is selected. |