Where in the Project Server database is the project site URLstored for a project? I can see the URLs by looking at Server Settings / Operational Policies / Project Sites but I need to pull information from lists on each project site for a report, so I need to find the project site URLs programmatically (i.e using SQL). I am familiar with querying the reporting database but I haven’t been able to find a field or table where these URLs are stored, in either the reporting or the published databases. Has anyone figured this out?
The field you are search for is called ProjectWorkspaceInternalHRef it can for example be found in view MSP_EpmProject_UserView.
The following query should provide you with project name and corresponding URL:
SELECT [ProjectUID], [ProjectName], [ProjectWorkspaceInternalHRef]