Environment: Project Server 2010
Requirement: Recently, we have got a requirement as part of EPM system health check data diagnostic for one of our client. For which, I have made a below small SQL query to detect the projects those are having the summary resource assignments. Hope this helps you.
–Query to display the Project Names having Summary Level resource Assignment
Select distinct P.ProjectName, COUNT(a.AssignmentUID)
MSP_EpmProject p INNER JOIN MSP_EpmTask t
INNER JOIN MSP_EpmAssignment A on t.taskUID=A.TaskUID
GROUP By P.ProjectName, t.taskuid