How to find Clarity PPM Database Columns within NSQL

Products

• Clarity PPM On Premise

Issue/Introduction

How do I find all occurrences where a database column is used within an NSQL query and elsewhere within PPM without having to look at each and every Object, Query, Portlet, etc. (i.e., I want to find everywhere within PPM that the database column with specific text is used).

Environment

Clarity PPM Application

Resolution

Here are some tips and tables from the PPM database schema that should help in getting the NSQL query data.

CMN_NSQL_QUERIES
Table contains the NSQL query.

CMN_GG_NSQL_QUERIES
Table contains the base information for the NSQL query (Administration -> Queries).

CMN_LISTOF_VALUES has dynamic lookup ID's and corresponding NSQL query can be found in cmn_nsql_queries using the column sql_text_id.

To find the the table name storing NSQL of dynamic lookups:
https://community.broadcom.com/communities/community-home/digestviewer/viewthread?MID=807667

CA Tuesday Tip: How to find out where an NSQL query is originating from:
https://community.broadcom.com/communities/community-home/digestviewer/viewthread?MID=720664#bm4d37ecd9-c14c-4ea5-9753-ebcd1731491b

If looking for a deeper analysis or need a more specific query, please use the Broadcom Communities.