Table and columns names

[sujithanagaraju]

Hi..Is there any possible way to get columns names for each table by using java script?

Thanks,

Sujitha nagaraju

[bernyalvarado]

You're welcome Sujitha! sujithanagarajup I'm glad it was of help.

I'll appreciate if you can mark the responses as helpful/correct so that it can be a reference for others looking for the same question.

Thanks,

Berny

[johnlbevan]

Thanks Berbny!

Based on your solution I've created a SQL script for generating the MS SQL Server table definition based on the sys_dictionary definition: SQL Code Gist on GitHub

--Language is T-SQL, but there is no option for that syntax here.  Use the above GIST link to see the code with correct syntax highlighting.

with sn (tableName, columnName, columnNameUI, max_length, internal_type) as 
(
	--See https://docs.servicenow.com/bundle/kingston-application-development/page/integrate/odbc-driver/concept/c_ODBCDrvrSQL20082012.html for info on ODBC driver & setting up a linked server
	select name, element, column_label, max_length, internal_type 
	from openquery(SERVICENOW , '
			select name
			, element --database column name
			, column_label --UI name
			,CAST(max_length as Decimal(38,0)) max_length --could convert to string here; but this makes it more reusable
			,internal_type
			from sys_dictionary
			where name in (''sys_user'', ''task'') --amend to only output those tables you want; included here since queries can be slow
	' ) 
	where element > ''
)
select 'create table ' + tableName + '('
+ stuff((
	SELECT char(10) + char(9) + ',' + quotename(columnName) + ' '
	+ case internal_type
		when 'boolean' then 'bit'
		when 'choice' then 'nvarchar(' + cast(max_length as nvarchar) + ')'  --just a string
		when 'collection' then 'nvarchar(' + cast(max_length as nvarchar) + ')'  --just a string
		when 'domain_id' then 'nvarchar(32)' --same as GUID
		when 'email' then 'nvarchar(' + cast(max_length as nvarchar) + ')' --just a string
		when 'float' then 'float'
		when 'glide_date' then 'date'
		when 'glide_date_time' then 'datetime'
		when 'GUID' then 'nvarchar(32)' --'uniqueidentifier' --could hold as uniqueidentifer, but then we'd need to convert values like so when populating: cast(stuff(stuff(stuff(stuff(COLUMN_NAME,21,0,'-'),17,0,'-'),13,0,'-'),9,0,'-') as uniqueidentifier)
		when 'integer' then 'int'
		when 'multi_two_lines' then 'nvarchar(' + cast(max_length as nvarchar) + ')'
		when 'numeric' then 'numeric(38,0)' --not sure if we need to do something special for precision...
		when 'password' then 'nvarchar(' + cast(max_length as nvarchar) + ')' --just a string
		when 'ph_number' then 'nvarchar(' + cast(max_length as nvarchar) + ')' --just a string
		when 'reference' then 'nvarchar(32)' --same as GUID
		when 'string' then 'nvarchar(' + cast(max_length as nvarchar) + ')'
		when 'sys_class_name' then 'nvarchar(' + cast(max_length as nvarchar) + ')' --just a string
		when 'url' then 'nvarchar(' + cast(max_length as nvarchar) + ')' --just a string
		when 'user_image' then 'nvarchar(' + cast(max_length as nvarchar) + ')'  --just a string
		when 'user_roles' then 'nvarchar(' + cast(max_length as nvarchar) + ')'  --just a string
		else 'nvarchar(max) /* todo: define type mapping for ''' + internal_type + ''' */' 
	end
	+ ' /* ' + replace(columnNameUI,'*/','* /') + ' */ '
	from sn c
	where c.tableName = t.tableName
	FOR XML PATH(''), TYPE
).value('.','varchar(max)'),3,1,' ')
+ '
)'
from sn t
group by t.tableName

Output (e.g. for sys_user table) looks like this:

create table sys_user(
	 [last_position_update] datetime /* Last position update */ 
	,[on_schedule] nvarchar(40) /* On schedule */ 
	,[latitude] float /* Latitude */ 
	,[sys_class_name] nvarchar(80) /* Class */ 
	,[user_name] nvarchar(80) /* User ID */ 
	,[user_password] nvarchar(100) /* Password */ 
	,[preferred_language] nvarchar(3) /* Language */ 
	,[password_needs_reset] bit /* Password needs reset */ 
	,[country] nvarchar(50) /* Country code */ 
	,[introduction] nvarchar(40) /* Prefix */ 
	,[first_name] nvarchar(50) /* First name */ 
	,[middle_name] nvarchar(50) /* Middle name */ 
	,[last_name] nvarchar(50) /* Last name */ 
	,[name] nvarchar(255) /* Name */ 
	,[phone] nvarchar(40) /* Business phone */ 
	,[mobile_phone] nvarchar(40) /* Mobile phone */ 
	,[home_phone] nvarchar(40) /* Home phone */ 
	,[email] nvarchar(100) /* Email */ 
	,[title] nvarchar(60) /* Title */ 
	,[gender] nvarchar(40) /* Gender */ 
	,[roles] nvarchar(255) /* Roles */ 
	,[time_zone] nvarchar(40) /* Time zone */ 
	,[accumulated_roles] nvarchar(4000) /* Accumulated roles */ 
	,[date_format] nvarchar(40) /* Date format */ 
	,[time_format] nvarchar(40) /* Time format */ 
	,[last_login] date /* Last login */ 
	,[last_login_time] datetime /* Last login time */ 
	,[locked_out] bit /* Locked out */ 
	,[last_password] nvarchar(40) /* Last password */ 
	,[last_login_device] nvarchar(40) /* Last login device */ 
	,[manager] nvarchar(32) /* Manager */ 
	,[notification] int /* Notification */ 
	,[calendar_integration] int /* Calendar integration */ 
	,[default_perspective] nvarchar(32) /* Default perspective */ 
	,[photo] nvarchar(40) /* Photo */ 
	,[location] nvarchar(32) /* Location */ 
	,[department] nvarchar(32) /* Department */ 
	,[edu_status] nvarchar(40) /* EDU Status */ 
	,[company] nvarchar(32) /* Company */ 
	,[source] nvarchar(255) /* Source */ 
	,[street] nvarchar(255) /* Street */ 
	,[city] nvarchar(40) /* City */ 
	,[zip] nvarchar(40) /* Zip / Postal code */ 
	,[state] nvarchar(40) /* State / Province */ 
	,[employee_number] nvarchar(40) /* Employee number */ 
	,[vip] bit /* VIP */ 
	,[building] nvarchar(32) /* Building */ 
	,[active] bit /* Active */ 
	,[failed_attempts] int /* Failed login attempts */ 
	,[cost_center] nvarchar(32) /* Cost center */ 
	,[sys_domain] nvarchar(32) /* Domain */ 
	,[sys_id] nvarchar(32) /* Sys ID */ 
	,[sys_updated_on] datetime /* Updated */ 
	,[sys_updated_by] nvarchar(40) /* Updated by */ 
	,[sys_created_by] nvarchar(40) /* Created by */ 
	,[sys_created_on] datetime /* Created */ 
	,[sys_mod_count] numeric(38,0) /* Updates */ 
	,[ldap_server] nvarchar(32) /* LDAP server */ 
	,[schedule] nvarchar(32) /* Schedule */ 
	,[longitude] float /* Longitude */ 
	,[geolocation_tracked] bit /* Geolocation tracked */ 
	,[sso_source] nvarchar(128) /* SSO Source */ 
	,[web_service_access_only] bit /* Web service access only */ 
	,[internal_integration_user] bit /* Internal Integration User */ 
	,[x_bmgr_support_ent_bomgar_username] nvarchar(80) /* Bomgar Username */ 
	,[u_org_structure] nvarchar(1000) /* Org Structure */ 
	,[agent_status] nvarchar(40) /* Work agent status */ 
)

[bernyalvarado]

That's interesting! What will be the purpose of reproducing the same table structure in ServiceNow at an external SQL DB?

Thanks,

Berny

[johnlbevan]

Hey Berny,

Benefits are:

- Out of system reporting.

- Clearly documented (in code form) SN table definitions.

More info on out-of-system reporting

Our company has a suite of BI tools for generating reports, dashboards, etc.

Whilst SN has functionality to do these things itself:

- our skillset is in our BI suite (i.e. our BI guys don't know glide/javascript)

- if we want to mix in data from other systems we can do this in BI

- we keep a consistent toolset for all reporting needs

That's not to say we don't use SN's abilities; but for anything complex, or anything seen by people who aren't day to day users of SN (e.g. upper management) it makes more sense to go the BI route.

How this logic helps with this reporting

We take nightly copies of the SN data so that our BI queries can run over a local database (i.e. as this is then significantly faster than working over ODBC / web services when dealing with large amounts of data, and allows for more complex queries in a more familiar query language).

Our DBA had been figuring out table definitions by pulling back records of interest, resolving the occasional number precision error (ref: https://docs.servicenow.com/bundle/kingston-application-development/page/integrate/odbc-driver/task/t_ConfiguringSQLServer.html), and when there were truncation errors increasing field lengths.  By having those definitions automatically generated it should save a lot of effort for such tasks.  

Future Improvements

As I learn more about what info's in SN I may be able to improve this functionality; both in terms of the table definitions and creating the import jobs.  i.e. Figure out if columns should be nullable / find out if I can define precision as well as scale for numbers, build in automatic conversion for GUIDs (i.e. so we hold as unique identifiers & convert then on import by adding hyphens and then casting), generate foreign key relationships (again to build self-documenting code and also improve performance for joins).  As those improvements come I'll be updating the GIST linked above, so the community can make use of this should they have the need.

Cheers,

JB

[Daniel Draes]

Nice, what do you need that for?