Reverse Engineering PeopleTools Tables

PeopleTools tables are the tables in your PeopleSoft environment that store PeopleTools meta-data - data about the underlying development platform for your environment. So this includes things like fields, records, pages, components, menus, and portal structures.

Access to these tables is available in every PeopleTools installation through application designer. But how do you which tables are which and what they are used for? This comes down to a bit of reverse engineering.

This article explains some ways to find out what certain PeopleTools tables do.

Naming convention for PeopleTools tables

The majority of PeopleTools tables start with PS and do not have an underscore between PS and the table name (e.g. PSRECNAME). You also don't need to prefix the table name with PS in this case as the record definition and table names match.

To see what I mean, open up PSRECDEFN in application designer and have a look at the Non-Standard SQL Table Name field on the Record Type tab:

non-standard-sql-table-name.png

Ordinary application tables such as the INSTALLATION table do not have a non-standard SQL name set which is why you need to prefix PS_ to them when you query them out of the database. There are some strange exceptions to this, notably some of the process scheduler tables such as the process definition table, PRCSDEFN which has the a non-standard SQL name of PS_PRCSDEFN.

Incidentally, if you want to query which table record definitions have non-standard SQL table names, use the SQLTABLENAME field in PSRECDEFN like so:

select RECNAME, RECDESCR, SQLTABLENAME
from PSRECDEFN
where SQLTABLENAME <> ' '
and RECTYPE = '0'
order by RECNAME;

Note that not every PeopleTools record has a description just to keep you guessing ;)

Identifying PeopleTools tables through Data mover Scripts

On your PeopleTools file server, if you have access to %PS_HOME%\scripts you should be able to find the data mover (.dms) scripts delivered with the PeopleSoft installation. Note that %PS_HOME% refers to the base installation folder of PeopleSoft and will vary.

The DMS script mvprdexp.dms contains a full list of PeopleTools tables and pt_release_export.dms lists the core PeopleTools tables. Both scripts group PeopleTools tables by type.For example, PeopleTools tables for records & indexes, fields and translates, field formats, pages, menus, components etc.

Identifying PeopleTools tables through PeopleSoft Query

If you run the PeopleSoft Query application (not through the PIA) either using Go > Query in application designer or starting the executable, psqed.exe, you can get it to show you access groups.

To do this, choose View > Preferences > Component View tab and select the Show Access Groups tab:

peoplesoft-query-show-access-groups.png

Your database window where you used to select record definitions should now be grouped. Scroll down and find the PeopleTools access group PT_ACCESS_GROUP and expand the folder.

This should now show you the PeopleTools tables by grouping (e.g. XMLPUBLISHER for XML Publisher, RECORD_DEFINITION for Record Definition etc).

It does not appear that you can do this through Query Manager in the PIA.

Resources on the Internet

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License