Spotfire: How to directly query the Spotfire database to find all references for a particular library object. 'Find All References' function.

How to directly query the Spotfire database to find all references for a particular library object. 'Find All References' function.

Caveat:

The below solution applies to the pre-v12 schema of the Spotfire database, so the below solution will not work with the currently supported versions of Spotfire. To use the below solution, you will need to adjust the below query to make it compatible with the database schema of the modern Spotfire versions. Due to the way in which content and design of the Spotfire database has changed since this article was written, it is possible that the below solution is no longer usable, no longer of value. In addition, please see the below 'Disclaimer'.
 

In the Information Designer, the function 'Find All References' allows you to see all references/dependencies on a particular Library object (Information Link, Column, Data Source). This is useful for ensuring that changes to a schema or information model are correctly managed and all dependent analyses can be properly updated. These references are stored in the Spotfire application database, and the database can also be queried directly, to facilitate a larger number of queries and export the data for external consumption. 

Resolution:

The Spotfire database contains all library content in the following folders starting with LIB_. 

Based upon these tables, the following example query (for Microsoft SQL Server) will return all dependencies for a particular object in the Spotfire Library whose unique GUID has been provided in the query's WHERE clause:

 select i.TITLE as [Item Name], i.ITEM_ID as [ITEM GUID], it.LABEL as [Item Type], parent.TITLE as [Parent Name], i.PARENT_ID as [Parent GUID] from LIB_RESOLVED_DEPEND rd join LIB_ITEMS i on i.ITEM_ID = rd.DEPENDENT_ID join LIB_ITEMS parent on parent.ITEM_ID = i.PARENT_ID join LIB_ITEM_TYPES it on it.TYPE_ID = i.ITEM_TYPE where REQUIRED_ID='fc28001a-e44b-4c99-999a-38d051fc0a58' -- Update with the desired GUID from your library or REQUIRED_ID in (select dependent_id from LIB_RESOLVED_DEPEND where REQUIRED_ID='fc28001a-e44b-4c99-999a-38d051fc0a58') -- Update with the desired GUID from your library 

Before executing this query, update the GUID used in its WHERE clause (REQUIRED_ID='fc28001a-e44b-4c99-999a-38d051fc0a58') to match the GUID of the item you wish to investigate.  You can find an object's GUID by right clicking on the object in the Information Designer and selecting "Copy ID".

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 TIBCO 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 libraryLIB_RESOLVED_DEPEND - Each object's dependencies on other objectsLIB_ITEM_TYPES - A translation of the ITEM_TYPE GUID to an English version (folder, analysis, column, etc.)