Reference

Endpoint Get Table Columns Admin Use Sys Dictionary

Name

get_table_columns_admin

Description

Get a list of columns by quering sys_dictionary. To read data from this table make sure user has read permission to sys_dictionary table. [API reference]

Parameters

Parameter Required Options
Name: TableName

Label: TableName

 YES
Name: PagingRowCount

Label: Page Size (Default=2000, Max=10000)

Max can be 10000 but it may decrease performance if you fetching less than 10K rows in some requests. 		YES
Name: PagingOffset

Label: PagingOffset

 YES

Output Columns

Label Data Type (SSIS) Data Type (SQL) Length Description
sys_id DT_WSTR nvarchar(500) 500
name DT_WSTR nvarchar(50) 50
element DT_WSTR nvarchar(100) 100
internal_type DT_WSTR nvarchar(100) 100
max_length DT_WSTR nvarchar(50) 50
column_label DT_WSTR nvarchar(100) 100
sys_name DT_WSTR nvarchar(100) 100
comments DT_WSTR nvarchar(100) 100
calculation DT_WSTR nvarchar(500) 500
dynamic_ref_qual DT_WSTR nvarchar(100) 100
choice_field DT_WSTR nvarchar(100) 100
function_field DT_WSTR nvarchar(50) 50
sys_updated_on DT_WSTR nvarchar(100) 100
spell_check DT_WSTR nvarchar(50) 50
reference_cascade_rule DT_WSTR nvarchar(100) 100
reference DT_WSTR nvarchar(100) 100
sys_updated_by DT_WSTR nvarchar(50) 50
read_only DT_WSTR nvarchar(50) 50
sys_created_on DT_WSTR nvarchar(100) 100
array_denormalized DT_WSTR nvarchar(50) 50
element_reference DT_WSTR nvarchar(50) 50
reference_key DT_WSTR nvarchar(100) 100
reference_qual_condition DT_WSTR nvarchar(100) 100
xml_view DT_WSTR nvarchar(50) 50
dependent DT_WSTR nvarchar(100) 100
internal_type_link DT_WSTR nvarchar(500) 500
sys_created_by DT_WSTR nvarchar(50) 50
use_dependent_field DT_WSTR nvarchar(50) 50
delete_roles DT_WSTR nvarchar(100) 100
virtual_type DT_WSTR nvarchar(100) 100
active DT_WSTR nvarchar(50) 50
choice_table DT_WSTR nvarchar(100) 100
foreign_database DT_WSTR nvarchar(100) 100
sys_update_name DT_WSTR nvarchar(500) 500
unique DT_WSTR nvarchar(50) 50
dependent_on_field DT_WSTR nvarchar(100) 100
dynamic_creation DT_WSTR nvarchar(50) 50
primary DT_WSTR nvarchar(50) 50
sys_policy DT_WSTR nvarchar(100) 100
next_element DT_WSTR nvarchar(100) 100
virtual DT_WSTR nvarchar(50) 50
widget DT_WSTR nvarchar(100) 100
use_dynamic_default DT_WSTR nvarchar(50) 50
sizeclass DT_WSTR nvarchar(100) 100
mandatory DT_WSTR nvarchar(50) 50
sys_class_name DT_WSTR nvarchar(100) 100
dynamic_default_value DT_WSTR nvarchar(100) 100
write_roles DT_WSTR nvarchar(100) 100
array DT_WSTR nvarchar(50) 50
audit DT_WSTR nvarchar(50) 50
read_roles DT_WSTR nvarchar(100) 100
sys_scope_link DT_WSTR nvarchar(500) 500
sys_scope_value DT_WSTR nvarchar(50) 50
create_roles DT_WSTR nvarchar(100) 100
dynamic_creation_script DT_WSTR nvarchar(100) 100
defaultsort DT_WSTR nvarchar(100) 100
use_reference_qualifier DT_WSTR nvarchar(50) 50
display DT_WSTR nvarchar(50) 50
reference_floats DT_WSTR nvarchar(50) 50
sys_mod_count DT_WSTR nvarchar(50) 50
default_value DT_WSTR nvarchar(100) 100
staged DT_WSTR nvarchar(50) 50
reference_type DT_WSTR nvarchar(100) 100
sys_package_link DT_WSTR nvarchar(500) 500
sys_package_value DT_WSTR nvarchar(50) 50
formula DT_WSTR nvarchar(100) 100
attributes DT_WSTR nvarchar(100) 100
choice DT_WSTR nvarchar(100) 100
reference_qual DT_WSTR nvarchar(100) 100
table_reference DT_WSTR nvarchar(50) 50
text_index DT_WSTR nvarchar(50) 50
function_definition DT_WSTR nvarchar(100) 100
sys_scope DT_WSTR nvarchar(100) 100
sys_package DT_WSTR nvarchar(100) 100
If the column you are looking for is missing, consider customizing ServiceNow Connector.

Input Columns

Label Data Type (SSIS) Data Type (SQL) Length 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 or in API Destination SSIS Data Flow components to read or write data.

API Source

API Source - ServiceNow
Allows the reading and writing of incidents, attachments, and more in ServiceNow without coding! The ZappySys ServiceNow API Connector provides easy-to-use, high-performance API access.
ServiceNow
Get Table Columns Admin Use Sys Dictionary
Required Parameters
TableName Fill-in the parameter...
Filter Fill-in the parameter...
SSIS API Source - Read from table or endpoint

API Destination

API Destination - ServiceNow
Allows the reading and writing of incidents, attachments, and more in ServiceNow without coding! The ZappySys ServiceNow API Connector provides easy-to-use, high-performance API access.
ServiceNow
Get Table Columns Admin Use Sys Dictionary
Required Parameters
TableName Fill-in the parameter...
Filter Fill-in the parameter...
SSIS API Destination - Access table or endpoint

ODBC application

Use these SQL queries in your ODBC application data source:

Get all columns of all tables (can be slow) 

SELECT * FROM get_table_columns_admin --requires readonly permissions on sys_dictionary and sys_db_object tables

SQL Server

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

Get all columns of all tables (can be slow) 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_table_columns_admin --requires readonly permissions on sys_dictionary and sys_db_object tables';

EXEC (@MyQuery) AT [LS_TO_SERVICENOW_IN_GATEWAY];