This will generate a membership list of all collections for a given computer. If you are running this in SQL Management Studio, either declare and set the @variable or you can put the computer name directly in the where section. If you will use SSRS, then this will serve as a parameter for your report.
DECLARE @Variable nvarchar(15) SET @Variable = 'computer_name' select distinct sys.netbios_name0 as 'Computer Name', v_collection.name as 'Collection Name', v_collection.collectionid as 'Collection ID' from v_fullcollectionmembership join v_collection on v_collection.collectionid = v_FullCollectionMembership.collectionid join v_r_system sys on sys.resourceid = v_fullcollectionmembership.resourceid where sys.netbios_name0 like @Variable order by v_collection.name
This will display computer name, collection name, and collection ID.