When implementing Resource management in a Project Server environment, it can be useful to a PMO administrator to know which projects contain local resource assignments as opposed to the use of only Enterprise resources.
For an On-Premise installation of Project Server 2010 or 2013, where access to the underlying databases is possible (as opposed to Project Online where it is not accessible), the following SQL query will list the Project Names or projects that have local resources assigned to tasks.
USE -- ProjectServerDatabaseName
FROM [MSP_EpmAssignment_UserView] AS Assn
inner join MSP_EPMResource_Userview as Res on Assn.ResourceUID = Res.ResourceUID
inner join MSP_EPMProject_UserView AS Prj on Assn.ProjectUID = Prj.ProjectUID
Where Res.ResourceName like 'Generic Local Resource'
Order By Prj.ProjectName