PeopleSoft Query Tables - The Complete List [Explanations]

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 into 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 into 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 of 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 is available at PeopleTools > Utilities > Administration > Query Administration
PSQRYLINK		The relationships to child queries are maintained in this record.
PSQRYPREFS		By looking at 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.