Often times on the ServiceNow platform, you want to look at the use of columns on a table. While you could easily build a report, that presents a challenge because you want to slice across all of the fields on a table rather than looking at a distinct set of values across one field on the table.
I am sure you have your own reasons for this type of report, but here are some potential uses:
- Platform Hygiene
- Removing fields with little adoption / use
- Removing tables with little adoption / use
The reporting engine doesn't really allow for this type of reporting. But surely this must be possible - right?
The platform has a feature that has a slightly different intended use but can be leveraged to assist with this type of reporting.
Enter sys_column_stats_registry [Column Statistics Registry]. This table is where you specify which table, and which field(s) you want to generate a report for.
- Specify the table name, if you are working with a table that is extended you will need to input both the parent and child class.
- Specify a specific field name or use the wildcard
*to scan all fields on the table - Submit the record and return to the list of records
- Use the List Related Link: "Collect statistics now" which will invoke a progress worker.
- Visit the
sys_column_statstable to view the collected results. Depending on your table size, this could take some time for the progress worker to complete.
Results
On the sys_column_stats table the statistics are displayed:
Null count: fields with no value set
Not null count: fields with a value set
Cardinality: a count of distinct values in a table
I find it best to join this data with the dictionary data, this way you have access to the field type and the field label.
This is an excellent tool to have. Give it a try and I'd love to hear your use cases for this!