Fetching a list of columns of a database view

[DanyloI]

Hello colleagues,

I'm trying to find a way to fetch columns of database views retrieved from /api/now/table/sys_db_view.

My original assumption was that /api/now/table/sys_dictionary should suite this purpose. But unfortunately it's not the case, it contains only columns of tables, not views.

Maybe someone can suggest a table (or API), where these columns can be found.

Thank you,

Danylo

[randyr1400]

did you find a way to achieve this? 

[Bert_c1]

The 'sys_db_view_table' field: 'view' is a reference to the sys_db_view table, 'sys_db_view_table_field' table has a a reference field: 'view_table' to the sys_db_view_table. Based on this relationship you can determine the fields for each view_table in the database view.

[randyr1400]

Thanks @Bert_c1! I really appreciate your insight 🙌

[Bert_c1]

@randyr1400, @DanyloI Here's a script that admin can run in Scripts - Background to get a list if view table fields.

// Get database view table fields.
var dbvName = 'customer_products';	// name of desired database view
getViewFields(dbvName);

function getViewFields(vName){
	var dbv = new GlideRecord('sys_db_view');
	dbv.addQuery('name', dbvName);
	dbv.query();
	if (!dbv.next()){
		gs.info("DBViewFields: No datbase view found named: " + dbvName);
		return;
	}
	var dbvID = dbv.sys_id.toString();
	// Get view tables
	var viewTables = [];
	var viewTablesName = [];
	var dbvt = new GlideRecord('sys_db_view_table');
	dbvt.addQuery('view', dbvID);
	dbvt.query();
	while (dbvt.next()) {
		viewTablesName.push(dbvt.table.getDisplayValue());
		viewTables.push(dbvt.sys_id.toString());
		gs.info('DBViewFields: For view named: ' + vName + ' found view table: ' + dbvt.table + ', sys_id: ' + dbvt.sys_id);
	}
	// Get view table fields
	for (i = 0; i < viewTables.length; i++) {
		var dbvtf = new GlideRecord('sys_db_view_table_field');
		dbvtf.addQuery('view_table', viewTables[i]);
		dbvtf.query();
		while (dbvtf.next()) {
			gs.info('DBViewFields: table: ' + dbvtf.table + ' has field: ' + dbvtf.field);
		}
	}
}

When no View fields are defined for a table, I believe those from the Default View for that table are used.