EndPoint Get Table Columns

Name

get_table_columns

Description

Get a list of columns of a table [API reference]

Parameters

Parameter	Label	Required	Options	Description
TableName	TableName	YES		Table name

Output Columns

Label	Data Type (SSIS)	Data Type (SQL)	Length	Raw
name	`DT_WSTR`	`nvarchar(128)`	128	`False`
element	`DT_WSTR`	`nvarchar(128)`	128	`False`
label	`DT_WSTR`	`nvarchar(96)`	96	`False`
internal_type	`DT_WSTR`	`nvarchar(128)`	128	`False`
max_length	`DT_I8`	`bigint`		`False`
column_type	`DT_WSTR`	`nvarchar(128)`	128	`False`
type	`DT_WSTR`	`nvarchar(60)`	60	`False`
filterable	`DT_BOOL`	`bit`		`False`
reference_display_field	`DT_WSTR`	`nvarchar(60)`	60	`False`
reference_attributes_display_field	`DT_WSTR`	`nvarchar(60)`	60	`False`
reference_attributes_ref_ac_columns	`DT_WSTR`	`nvarchar(108)`	108	`False`
reference_attributes_ref_ac_columns_search	`DT_WSTR`	`nvarchar(80)`	80	`False`
reference_attributes_ref_ac_display_value	`DT_BOOL`	`bit`		`False`
reference_attributes_ref_auto_completer	`DT_WSTR`	`nvarchar(80)`	80	`False`
canmatch	`DT_BOOL`	`bit`		`False`
cangroup	`DT_BOOL`	`bit`		`False`
max_unit	`DT_WSTR`	`nvarchar(80)`	80	`False`
mandatory	`DT_BOOL`	`bit`		`False`
reference	`DT_WSTR`	`nvarchar(100)`	100	`False`
default	`DT_WSTR`	`nvarchar(255)`	255	`False`
base_type	`DT_WSTR`	`nvarchar(32)`	32	`False`
read_only	`DT_BOOL`	`bit`		`False`
hint	`DT_WSTR`	`nvarchar(388)`	388	`False`
attributes	`DT_WSTR`	`nvarchar(4000)`	4000	`False`
cansort	`DT_BOOL`	`bit`		`False`
multitext	`DT_BOOL`	`bit`		`False`
choice_type	`DT_I8`	`bigint`		`False`
choices	`DT_NTEXT`	`nvarchar(MAX)`		`False`

Input Columns

Label	Data Type (SSIS)	Data Type (SQL)	Length	Raw	Description
There are no Static columns defined for this endpoint. This endpoint detects columns dynamically at runtime.

Examples

SSIS

Use ServiceNow Connector in API Source component to read data or in API Destination component to read/write data:

Get Table Columns using API Source

ServiceNow

Get Table Columns

SSIS API Source - Read from table or endpoint

Get Table Columns using API Destination

ServiceNow

Get Table Columns

SSIS API Destination - Access table or endpoint

ODBC application

Use these SQL queries in your ODBC application data source:

Get the display value of a reference field

This example shows how to get related record(s) from the reference field. Basically, you need to know referenced table name for that referenced field. Then you can query by sys_id (Primary Key)

--STEP#1 returns incident number and related company (sys_id)
SELECT number,company FROM incident

--STEP#2 find related table for company field
select name,reference from get_table_columns order by 1 WITH(TableName='incident')

--STEP#3 query related table with sys_id returned in step#1
select sys_id, name  from core_company where sys_id='31bea3d53790200044e0bfc8bcbe5dec'

Get columns of a specific table

SELECT * FROM get_table_columns
where name = 'TableNameGoesHere'

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

Get the display value of a reference field

This example shows how to get related record(s) from the reference field. Basically, you need to know referenced table name for that referenced field. Then you can query by sys_id (Primary Key)

DECLARE @MyQuery NVARCHAR(MAX) = '--STEP#1 returns incident number and related company (sys_id)
SELECT number,company FROM incident

--STEP#2 find related table for company field
select name,reference from get_table_columns order by 1 WITH(TableName=''incident'')

--STEP#3 query related table with sys_id returned in step#1
select sys_id, name  from core_company where sys_id=''31bea3d53790200044e0bfc8bcbe5dec''';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_SERVICENOW_IN_DATA_GATEWAY];

Get columns of a specific table

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_table_columns
where name = ''TableNameGoesHere''';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_SERVICENOW_IN_DATA_GATEWAY];