Share via

T-SQL: Query The Enterprise Custom Fields In Project Server 2016 /2013

  • Article

Introduction

In this article, we will explain How to

  • Query the Enterprise Custom Fields In Project Server 2013 via T-SQL.
  • Query the Enterprise Custom Fields In Project Server 2016 via T-SQL.

You may also like to read Install and configure Project Server 2016.

Scenario

In Project Server, In some cases, you may need to export the Enterprise Custom Fields to Excel.
but unfortunately, there is no export option in the Enterprise Custom Fields page as shown below.

https://gallery.technet.microsoft.com/site/view/file/180170/1/Query%20the%20Enterprise%20Custom%20Fields%20From%20Project%20Server%20Database.gif

In this case, the available workaround is querying these data from the Project Server Database as we will mention in the next section.

Get the Enterprise Custom Fields In Project Server 2013 via T-SQL

In Project Server 2013, there are two Databases.

  • Project Web App Database.
  • SharePoint Content database.

The Project data is stored in the Project Web App Database.

  • The Project Web App Database has a predefined function called GetAllCustomFieldsInformation.
  • The GetAllCustomFieldsInformation is used to retrieve the related Enterprise Custom fields data like Name, EntityName, DataType ..etc.

To query the Enterprise Custom fields data.

Use [ProjectWebApp]
Select * FROM MFN_Epm_GetAllCustomFieldsInformation()

https://gallery.technet.microsoft.com/site/view/file/180173/1/MFN_Epm_GetAllCustomFieldsInformation().png

Unfortunately, in the above query, the DataType is represented as Integer ID that should be shown as the below corresponding data types.

https://gallery.technet.microsoft.com/site/view/file/180172/1/CustomFields%20DataType.png

Below is the list of  the corresponding Custom Field based on its Data type ID

 Data Type ID Data Type 
 Cost  9
 Date  4
 Duration  6
 Flag  17
 Number  15
 Text  21

Now let us query the Custom Field information with the Data type name

Use [ProjectWebApp-Demo]
SELECT CustomFieldName,EntityName,
case
when DataType = '9'  then  'Cost'
when DataType = '4'  then  'Date'
when DataType = '6'  then  'Duration'
when DataType = '17' then  'Flag'
when DataType = '15' then  'Number'
when DataType = '21' then  'Text'
end as  'DataType',
CreatedDate,ModificationDate
FROM MFN_Epm_GetAllCustomFieldsInformation()

Output

https://gallery.technet.microsoft.com/site/view/file/180175/1/Custom%20Field%20Data%20Type%20in%20Project%20Server%20Database.png

Get the Enterprise Custom Fields In Project Server 2016 via T-SQL

In Project Server 2016, there is no Project database, only single database (SharePoint Content Database) holds the project data and the content to facilitate the database operations, such as backup and restore, migration, etc.

Although there is a big change in the database, we still can use **GetAllCustomFieldsInformation **to get the Custom Fields list with a bit changes as mentioned below:

  • Because the same content database can hold multiple PWA instances, So you should filter the GetAllCustomFieldsInformation by **SiteID as a parameter **to scope the fields based on the specified PWA Instance Site ID.
  • To use  GetAllCustomFieldsInformation you should use the [pjrep] schema as [pjrep].GetAllCustomFieldsInformation.

To get the Site ID

Select  Id FROM [dbo].[Sites]

To query the Enterprise Custom fields data based on the site ID

SELECT * FROM
[pjrep].[MFN_Epm_GetAllCustomFieldsInformation] ('SiteID') -- Site ID ex: 59E51D34-8597-4348-8589-F4D37E1EB85E

Applies To

  • Project Server 2013 Database.
  • Project Server 2016 Database.

Conclusion

In this article, we have learned

  • How to get the Enterprise Custom Fields In Project Server 2013 via T-SQL.
  • How to get the Enterprise Custom Fields In Project Server 2016 via T-SQL.
  • How to get the Enterprise Custom Fields Datatype based on the TypeID via T-SQL.

See Also

Back To Top