How to query the Spotfire database to get the list of all analysis files using a particular data connection which is saved in a library

How to query the Spotfire database to get the list of all analysis files using a particular data connection which is saved in a library

You may need to find the list of all analysis files which uses a particular data connection which is saved in a library. For example, if there are changes to the data which will require an edit in the analysis files you may need a list of all files that require changes. With the help of query provided in resolution you will be getting the desired result. Use the following example query against the Spotfire database to generate a list of analysis files referencing the data connection:

select  
y.TITLE Analysis_File_Name 
from LIB_RESOLVED_DEPEND x 
join LIB_ITEMS y on x.DEPENDENT_ID = y.ITEM_ID
join LIB_ITEM_TYPES z on z.TYPE_ID = y.ITEM_TYPE
where REQUIRED_ID = '91ba2f17-03b7-44e7-821e-cde6dd56b01a' and z.DISPLAY_NAME='dxp'

Important notes:

• Before executing the query, replace the REQUIRED_ID value ('91ba2f17-03b7-44e7-821e-cde6dd56b01a') in the above query with GUID of the respective data connection saved in library.
• The above query is an example tested on Microsoft SQL Server.

Disclaimer: The content of this article is for informational purposes only. The subject material may change in any new versions with no notice and there is no responsibility by Spotfire to maintain or support future access to this internal application content. Modification of any internal application content is not recommended and can lead to an unsupported configuration.  It is not intended to be used "As Is" in a Production environment. Always test in a Development environment.

The key tables used in this particular query are:

• LIB_ITEMS - A row for each object in the library
• LIB_RESOLVED_DEPEND - Each object's dependencies on other objects
• LIB_ITEM_TYPES - A translation of the ITEM_TYPE GUID to an English version (Data Connection, Information Link etc.)