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
Output Columns
Label | Data Type (SSIS) | Data Type (SQL) | Length | Raw | Description |
---|---|---|---|---|---|
sys_id |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
name |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
element |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
internal_type |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
max_length |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
column_label |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
sys_name |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
comments |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
calculation |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
dynamic_ref_qual |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
choice_field |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
function_field |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
sys_updated_on |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
spell_check |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
reference_cascade_rule |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
reference |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
sys_updated_by |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
read_only |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
sys_created_on |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
array_denormalized |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
element_reference |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
reference_key |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
reference_qual_condition |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
xml_view |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
dependent |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
internal_type_link |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
sys_created_by |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
use_dependent_field |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
delete_roles |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
virtual_type |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
active |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
choice_table |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
foreign_database |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
sys_update_name |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
unique |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
dependent_on_field |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
dynamic_creation |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
primary |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
sys_policy |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
next_element |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
virtual |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
widget |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
use_dynamic_default |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
sizeclass |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
mandatory |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
sys_class_name |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
dynamic_default_value |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
write_roles |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
array |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
audit |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
read_roles |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
sys_scope_link |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
sys_scope_value |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
create_roles |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
dynamic_creation_script |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
defaultsort |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
use_reference_qualifier |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
display |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
reference_floats |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
sys_mod_count |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
default_value |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
staged |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
reference_type |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
sys_package_link |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
sys_package_value |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
formula |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
attributes |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
choice |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
reference_qual |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
table_reference |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
text_index |
DT_WSTR
|
nvarchar(50)
|
50 | False |
|
function_definition |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
sys_scope |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
sys_package |
DT_WSTR
|
nvarchar(100)
|
100 | 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 - Admin (Use sys_dictionary) using API Source
ServiceNow
Get Table Columns - Admin (Use sys_dictionary)

Get Table Columns - Admin (Use sys_dictionary) using API Destination
ServiceNow
Get Table Columns - Admin (Use sys_dictionary)

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 [LINKED_SERVER_TO_SERVICENOW_IN_DATA_GATEWAY];