Is there some simple SQL that can be executed in SQL Commander to query an AF database and get all attributes that are of type PIPoint data reference, returning the full attribute pathname and the tag referenced?
Your query looked good. If you only need a subset of attributes (i.e. do not need to return all attributes), you can consider filtering by path/attribute name.
If you are concerned about performance, you might be interested in this white paper: PI OLEDB Enterprise SQL Optimization.
In my system, I have rearranged the join orders and use OPTION (FORCE ORDER) to achieve a slight better query time. Perhaps you can test it to see if it makes a difference on your system:
SELECT eh.Path + eh.Name + '|' + ea.Name Attribute, ea.ConfigString PITagName, ea.*
FROM [Dev Support].[Asset].[ElementHierarchy] eh
INNER JOIN [Dev Support].[Asset].[ElementAttribute] ea ON ea.ElementID = eh.ElementID
INNER JOIN [System].[AF].[PlugIn] p ON ea.DataReferencePlugInID = p.ID
WHERE p.Name = 'PI Point'
ORDER BY Attribute ASC
OPTION (ALLOW EXPENSIVE, FORCE ORDER)
I have played around with this some more and I have something that seems to work. It might not be the best way to do this.
Retrieving data ...