How to directly query the Spotfire Server database to find all objects required by a particular library object

How to directly query the Spotfire Server database to find all objects required by a particular library object.

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).

There is often a need to find all objects that are required for a particular object (i.e., references in the other direction). For example, an Information Link is dependent upon all of the Column, Join, Filter, Procedure, and Data Source objects that are used in its definition. Being able to report on these required objects 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.

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

Based upon these tables, the following query will return all required objects for a particular object in the Spotfire Library whose unique GUID has been provided in the query's @itemID variable:

declare @itemID varchar(64)
set @itemID = 'd358c018-f34e-47b6-a1d1-e882641b7f0c' -- Update GUID of item here

select distinct i.TITLE as [Item Name], i.ITEM_ID as [ITEM GUID], it.LABEL as [Item Type], parent.TITLE as [Parent Folder Name], i.PARENT_ID as [Parent Folder GUID]
from LIB_RESOLVED_DEPEND rd
join LIB_ITEMS i on i.ITEM_ID = rd.REQUIRED_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 DEPENDENT_ID = @itemID
   or DEPENDENT_ID in (select REQUIRED_ID from LIB_RESOLVED_DEPEND where DEPENDENT_ID = @itemID)

Before executing this query, update the GUID used in the @itemID variable definition (set @itemID = 'd358c018-f34e-47b6-a1d1-e882641b7f0c') 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".

Note: This example query will list all objects required by the given object to two levels of depth: the direct requirements, and the requirements of those direct requirements.
 

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 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 (folder, analysis, column, etc.)