TFS2015 - List all user and shared queries with their owners

Question

I'm trying to list all user owned and shared queries along with their owners from a TFS2015 database and I cannot find the owner of the user owned queries, it is always displayed as a null. I found a similar topic on SO that returns shared queries, but when applying it to user owned queries, it does not return any owner. I went through the records inside the QueryItems table and I cannot find any field that would point to the owner of a query. Here's the SQL query I have been using so far:

   SELECT qi.Name as 'Query Name'
         ,c.DisplayPart as 'Query Owner',
          [fPublic],
          [fFolder],
          [fDeleted]
     FROM [Tfs_Development].[dbo].[QueryItems] qi
LEFT JOIN [Tfs_Development].[dbo].[Constants] c ON c.TeamFoundationId=qi.OwnerIdentifier
    WHERE fPublic=0 AND fFolder=1 And fDeleted=0

If there's a way to do this with REST API, that would be helpful as well.

Answer 1

For the Shared Queries the key should be WHERE fPublic=1 AND fFolder=0 And fDeleted=0

So, just try below sql query (it works on my side):

SELECT   qi.Name as 'Query Name',
         c.DisplayPart as 'Query Owner',
          [fPublic],
          [fFolder],
          [fDeleted]
     FROM [TFS_DefaultCollection].[dbo].[QueryItems] qi
     LEFT JOIN [TFS_DefaultCollection].[dbo].[Constants] c ON c.TeamFoundationId=qi.OwnerIdentifier
     WHERE fPublic=1 AND fFolder=0 And fDeleted=0

Of course you can also use the REST API to retrieve the information:

GET http://server:8080/tfs/DefaultCollection/{ProjectName}/_apis/wit/queries/Shared Queries?api-version=1.0&$depth=2

---

UPDATE:

Explanation for the keys :

fPublic=1 is for shared query, fPublic=0 is for private query;

fFolder=0 means the retrieved record is not a folder, fFolder=1 means the record is a folder (You can create folders to group the queries)

fDeleted=0 means the query is normal used, fDeleted=1 means the query is deleted.