As PeopleSoft is a metadata-driven application, all the building blocks including PeopleCode are stored within database tables.
And PS Query is no exception to this.
Knowing the tables underlying PS Queries will be handy when you have to write a query to fetch some details about Queries; for example, to see what other queries are there at your organisation that match a certain pattern or what other queries query a particular table.
In this post let’s see which are the meta tables that are used by PeopleSoft Queries.
| Record Name | Description | Remarks |
|---|---|---|
| PSQRYDEFN | Query Definition | The main Query table. |
| PSQRYDEFNLANG | Query Definition Alt. Language for PSQRYDEFN | The related language record to the PSQRYDEFN table. |
| PSQRYRECORD | Query Record | All the Records that are used in the Query are stored in this record. |
| PSQRYFIELD | Query Field | All the fields that are used in the Query are stored in this record. |
| PSQRYFIELDLANG | Query Field Alternate Language for PSQRYFIELD | This is the related language for PSQRYFIELD. So this comes to play only when you have a multi-language installation. This record allows the Heading text of the fields to be translated. |
| PSQRYCRITERIA | Query Criteria | All data represented on the Criteria page of the Query are stored in this record. |
| PSQRYBIND | Query Prompt | Data underlying the Prompt page of the Query comes from this record. |
| PSQRYBINDLANG | Query Prompt Alternate Lang. for PSQRYBIND | This is the related language for PSQRYBIND. So this comes to play only when you have a multi-language installation. This record allows the Heading text that appears on the Query Prompt dialog to be translated. |
| PSQRYEXPR | Query Expression | Stores data underlying the Expression page of the Query as well as expression-based criteria. |
| PSQRYSELECT | Query Select | Stores a summary of the Query like how many sub-queries there are, how many selects there are, how many criteria there are etc. |
| PSQRYXFORM | Query Transformation | Stores details entered on the Transformations page of the Query. So if you are looking at querying the Transformation XSL, this is where you would be looking. |
| PSQRYFIELDDEP | Query Field Dependency | Stores the Field dependency details entered on the Dependency page of the Query. These settings are only used for SQL pruning in Composite Queries. |
| PSQRYSTATS | Query RunTime Statistics | Stores Runtime statistics like the average number of fetched, how many times the query was executed etc. |
| PSQRYEXECLOG | Query RunTime Log | Stores information on the execution of queries including the date/time stamp of execution, the user who ran it, time taken for execution, number of rows fetched etc. |
| PSQRYDEL | Query Definition | This record keeps track to deleted Query Names and their version numbers. This is used by the VERSION App Engine. |
| PSQRYFAVORITES | Query Manager Favorites Table | If you add a Query to your favourites on the Quey Manager, it will be saved in this record. |
| PSQRYFLAGS | Query Global Flags Table | The Settings page of the Query Administration component available at PeopleTools > Utilities > Administration > Query Administration |
| PSQRYLINK | The relationships to child queries are maintained in this record. | |
| PSQRYPREFS | By looking the fields, this record seems to track if Auto-join is enabled and the name style (Description Only vs Name and Description). But I haven’t seen any entries in this table. |
|
| PSQRYTRANS | Each query that is run from Query Manager/Query Viewer is represented as a row in PSQRYTRANS. This is where the Query monitoring tool looks for orphaned rows to kill the query. |
|
| PSQRYACCLSTRECS | Query Access Record List | Populated by the Query Access List process. Stores details of which permission list has access to what records. |
Apart from these standard query tables, there are a few more that are relevant to queries – for example SCRTY_ACC_GRP is the PeopleSoft Query Access Group Table.
As you can see, some of these tables are not well documented. So if you have more info on any of them, please let me know in the comments.
