Was just asked to create a ConfigMgr query to show which collections a client is a member of:

Note: Collections do not replicate upwards, so running this on your Central for a Tier 2\3 client will not show anything. Either run it on the assigned site server, or from a higher up site server where all the collections are managed ...

SELECT     v_FullCollectionMembership.CollectionID, v_FullCollectionMembership.Name as [Client], v_Collection.Name AS [Collection Name],
                      v_FullCollectionMembership.SiteCode
FROM         v_FullCollectionMembership INNER JOIN
                      v_Collection ON v_FullCollectionMembership.CollectionID = v_Collection.CollectionID
WHERE V_FullCollectionMembership.Name = '<MACHINE NAME>'

This can even go in to a report, create the report, create a prompt called @ClientName, edit the above '<MACHINE NAME>' text (don't forget to strip the apostrophes out too) and replace with @ClientName, then replace the existing default query in the new report with the modified SQL statement from above.

You could even link this report to the built-in "All resources in a specific collection" report, a good idea as that report then links to another report showing you the advertisements for that client which can then be drilled in to even further to give you the "Status of a specific advertisement". Very handy if you first want to know what collections the client is a member of (for deployment troubleshooting), then go through to the advertisement information.

Read the complete post at http://wmug.co.uk/blogs/r0b/archive/2009/05/15/simple-sql-query-show-collections-a-client-is-a-member-of.aspx