GetTableInfo |
This method is used to retrieve details of a database table. This method can be tested through the Service Test Tool of Process Platform.
SOAP Request
<SOAP:Envelope xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">
<SOAP:Body>
<GetTableInfo xmlns="http://schemas.cordys.com/WS-AppServer/1.0">
<tableName>Customers</tableName>
<tableCatalog>Northwind</tableCatalog>
<tableSchema>dbo</tableSchema>
<tableType>TABLE</tableType>
<format>R1</format>
</GetTableInfo>
</SOAP:Body>
</SOAP:Envelope>
Request Parameters
Parameter |
Description |
Data Type |
Accepted Input Values |
|---|---|---|---|
tableName |
The name of the table for which metadata information is retrieved. If this is empty or not specified, then metadata information is retrieved for all the tables. |
String |
- |
tableCatalog |
The table name from which the table information should be extracted. If not specified, information is retrieved from all the catalogs. |
String |
- |
tableSchema |
The schema defined in the database. In SQL, this denotes the name of the owner of the database. |
String |
dbo |
tableType |
The type of the table retrieved. |
String |
TABLE |
format |
Determines whether complete information about the table keys and constraints are retrieved or not. |
String |
R1 - provides basic information about a table; R2 - provides complete information in a structured manner, differentiating the indexes, primary, and foreign keys. |
SOAP Response
<data>
<GetTableInfoResponse
xmlns="http://schemas.cordys.com/WS-AppServer/1.0" xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">
<tuple>
<old>
<TableInfo>
<tableCatalog>Northwind</tableCatalog>
<tableSchema>dbo</tableSchema>
<tableName>Customers</tableName>
<description/>
<Columns>
<Column>
<columnName>CustomerID</columnName>
<datatype>i4</datatype>
<precision>10</precision>
<scale>0</scale>
<autoIncrement>true</autoIncrement>
</Column>
<Column>
<columnName>CompanyName</columnName>
<datatype>string</datatype>
<size>40</size>
</Column>
<Column>
<columnName>ContactName</columnName>
<datatype>string</datatype>
<size>30</size>
</Column>
<Column>
<columnName>ContactTitle</columnName>
<datatype>string</datatype>
<size>30</size>
</Column>
<Column>
<columnName>Address</columnName>
<datatype>string</datatype>
<size>60</size>
</Column>
<Column>
<columnName>City</columnName>
<datatype>string</datatype>
<size>15</size>
</Column>
<Column>
<columnName>Region</columnName>
<datatype>string</datatype>
<size>15</size>
</Column>
<Column>
<columnName>PostalCode</columnName>
<datatype>string</datatype>
<size>10</size>
</Column>
<Column>
<columnName>Country</columnName>
<datatype>string</datatype>
<size>15</size>
</Column>
<Column>
<columnName>Phone</columnName>
<datatype>string</datatype>
<size>24</size>
</Column>
<Column>
<columnName>Fax</columnName>
<datatype>string</datatype>
<size>24</size>
</Column>
<Column>
<columnName>PK_Customers</columnName>
<SubColumns>
<columnName>CustomerID</columnName>
</SubColumns>
</Column>
</Columns>
<Indices>
<Index>
<description>City</description>
<indexNr>1</indexNr>
<columnName>City</columnName>
</Index>
<Index>
<description>CompanyName</description>
<indexNr>2</indexNr>
<columnName>CompanyName</columnName>
</Index>
<Index>
<description>PostalCode</description>
<indexNr>3</indexNr>
<columnName>PostalCode</columnName>
</Index>
<Index>
<description>Region</description>
<indexNr>4</indexNr>
<columnName>Region</columnName>
</Index>
<Index>
<description>PK_Customers</description>
<indexNr>5</indexNr>
<isPrimary>true</isPrimary>
<columnName>CustomerID</columnName>
</Index>
</Indices>
</TableInfo>
</old>
</tuple>
</GetTableInfoResponse>
</data>
For numeric fields, the method returns precision and scale tags. Precision is the maximum number of digits the field can store (including decimal part). Scale specifies the number of digits in the decimal part. If the numeric field is defined as auto increment field in the database then the method returns autoIncrement tag with value true.
Note:
The response will slightly differ when you set the format to R2 in the SOAP request. Apart from Columns and Indices, you will see an additional tag called Keys, as shown in the following sample.
<data>
<GetTableInfoResponse
xmlns="http://schemas.cordys.com/WS-AppServer/1.0" xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">
<tuple>
<old>
<TableInfo>
<tableCatalog>Northwind</tableCatalog>
<tableSchema>dbo</tableSchema>
<tableName>Customers</tableName>
<description/>
<Columns/>
<Keys>
<PrimaryKey>
<keyName>PK_Customers</keyName>
<SubColumns>
<columnName>CustomerID</columnName>
</SubColumns>
<Relation>
<tableName>CustomerCustomerDemo</tableName>
<keyName>FK_CustomerCustomerDemo_Customers</keyName>
<SubColumns>
<columnName>CustomerID</columnName>
</SubColumns>
<updateRule>nop</updateRule>
<deleteRule>nop</deleteRule>
</Relation>
<Relation>
<tableName>Orders</tableName>
<keyName>FK_Orders_Customers</keyName>
<SubColumns>
<columnName>CustomerID</columnName>
</SubColumns>
<updateRule>nop</updateRule>
<deleteRule>nop</deleteRule>
</Relation>
</PrimaryKey>
</Keys>
<Indices/>
</TableInfo>
</old>
</tuple>
</GetTableInfoResponse>
</data>
Response Parameters
Parameter |
Description |
|---|---|
tableInfo |
Information about the table categories |
tableName |
The name of the table |
tableCatalog |
The table name from which the table information is extracted |
tableSchema |
The schema defined in the database. In SQL, this denotes the name of the owner of the database. |
description |
A collective description of the table |
Columns |
A collection of columns |
Column |
Name of the column in the database |
Keys |
Lists the primary and foreign keys present in the table |
Indices |
Collection of indexes |
Index |
Indicates the unique key and primary key in the database table |