In this article, I gonna answer the following two questions
- Where Microsoft Project Server 2010 stores data?
- 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:
- Used to store online backups of data from the rest of the site.
- 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.
- 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.
- 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.
- 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'
- 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.