Query Custom Fields From Database in Project Server 2010

In this article, I gonna answer the following two questions

  1. Where Microsoft Project Server 2010 stores data?
  2. How you can retrieve the names of custom field?

Where Microsoft Project Server 2010 stores data? In Project Server 2010, the data is partitioned into five databases in Microsoft SQL Server:

  1. Archive.
    • Used to store online backups of data from the rest of the site.
  2. Draft.
    • Contains tables for editing unpublished projects from Microsoft Project Professional 2010 and the Project Server Interface Web services. Project data in the Draft database cannot be accessed by using Microsoft Project Web App. This database also hosts the tables that are used by the Project Queue.
  3. Published.
    • Contains all of the published projects. Published projects are visible in Project Web App. The Published database also contains tables that are specific to Project Web App (timesheets, models, views, and so on), and global data tables (which contain resources, custom fields, security definitions, and other metadata). This database also hosts the tables that are used by the Timesheet Queue.
  4. Reporting.
    • Contains the same data as the Published database, but optimized for generating reports and online analytical processing (OLAP) cubes. Data in the Reporting database is updated almost in real-time, is comprehensive, and is optimized for read-only report generation.
  5. SharePoint Content database.
    • Contains project site content such as custom pages, workflows, management settings, documents, and lists of issues, risks, and commitments.

How you can retrieve the names of custom field? 

To find the custom field names in the column pool tables, you can use the following query using Reporting database in Microsoft SQL Server.

SELECT CustomFieldName, ColumnPoolTableName, ColumnPoolColumnName
FROM MFN_Epm_GetAllCustomFieldsInformation()
WHERE EntityName = N'EntityType'

Note:

  • Entity type value is as  ‘Task’ or ‘Project’ ….. etc.
  • The MFN_Epm_GetAllCustomFieldsInformation is a user defined function (UDF) in the Project Server 2010 RDB.

Enjoy 🙂

Advertisements

3 thoughts on “Query Custom Fields From Database in Project Server 2010

  1. Hi Mohammed:

    Thank you very much for this information and I wanted to ask you something related to this. Do you know any table in RDB or other Project Server database where I could find the relationship between a project and its project site id (MSP_EpmProject_UserView shows the URL but not the site ID in the Sharepoint Content database).

    Thank you very much

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s