How to find out all dependent elements of an information link in a library in Spotfire 11.4 or below

Problem:

Spotfire admins might be interested in analyzing the dependent elements (e.g. columns, filters, joins) of information links when cleaning up and moving items in a library. Is there an easy way to retreive this information and preferably analyze it using Spotfire?

Resolution:

The following SQL can be ran against the Spotfire server database to retrieve the name and full library path of all information links in a library, as well as the name, item type, and full library path of all dependent elements of each information links.

• The SQL was created for Oracle database and for Spotfire 11.4 LTS and below.
• You need to change the SPOTFIRE_DB_SCHEMA to your organization's Oracle schema.
• SQL can be execute from Spotfire Connector and then analyzed using Spotfire in the way you like.

WITH
List1(ITEM_ID, TITLE, LIBRARY_PATH, PARENT_ID) as(
SELECT
A.ITEM_ID,
A.TITLE,
CAST(A.TITLE AS nvarchar2(2000)) AS LIBRARY_PATH, 
A.Parent_ID
FROM 
SPOTFIRE_DB_SCHEMA.LIB_ITEMS A
LEFT JOIN SPOTFIRE_DB_SCHEMA.LIB_ITEM_TYPES lit on lit.TYPE_ID=A.ITEM_TYPE
WHERE
lit.DISPLAY_NAME='folder' and A.hidden=0
),
List2(ITEM_ID, TITLE, LIBRARY_PATH,PARENT_ID) as(
SELECT
ITEM_ID,
TITLE,
CAST(TITLE AS nvarchar2(1000)) AS LIBRARY_PATH, 
Parent_ID
FROM List1
WHERE TITLE = 'root'
UNION ALL
SELECT
A.ITEM_ID,
A.TITLE,
REPLACE(CAST((B.LIBRARY_PATH || '/' || A.TITLE) AS nvarchar2(1000)),'root', '') AS LIBRARY_PATH, 
A.Parent_ID
FROM List1 A inner join List2 B ON A.PARENT_ID = B.ITEM_ID
),
List3(item_id, title, parent_id, display_name) as(
SELECT A.item_id, A.title, A.parent_id, lit.DISPLAY_NAME
FROM SPOTFIRE_DB_SCHEMA.LIB_ITEMS A
LEFT JOIN SPOTFIRE_DB_SCHEMA.LIB_ITEM_TYPES lit on lit.TYPE_ID=A.ITEM_TYPE
WHERE lit.DISPLAY_NAME in ('query')
),
List4(QUERY_ITEM_ID,QUERY_TITLE, QUERY_PATH,required_id) as(
SELECT
A.item_id as QUERY_ITEM_ID,
A.TITLE as QUERY_TITLE,
REPLACE(CAST((B.LIBRARY_PATH || '/' || A.TITLE) AS nvarchar2(1000)),'root', '') AS QUERY_PATH,
rd.required_id
FROM List3 A
left join List2 B on A.parent_id=B.item_id 
LEFT JOIN SPOTFIRE_DB_SCHEMA.LIB_RESOLVED_DEPEND rd on rd.dependent_id=A.item_id 
),
List5(element_id,element_title,element_path,element_item_type) as(
SELECT a.item_id as element_id,
a.title as element_title, 
REPLACE(CAST((B.LIBRARY_PATH || '/' || A.TITLE) AS nvarchar2(1000)),'root', '') as element_path,
lit.display_name as element_item_type
FROM SPOTFIRE_DB_SCHEMA.lib_items A
LEFT JOIN List2 B on A.parent_id=B.item_id
LEFT JOIN SPOTFIRE_DB_SCHEMA.LIB_RESOLVED_DEPEND rd on rd.dependent_id=A.item_id
LEFT JOIN SPOTFIRE_DB_SCHEMA.LIB_ITEM_TYPES lit on lit.TYPE_ID=A.ITEM_TYPE
WHERE lit.DISPLAY_NAME in ('column','filter','procedure','join')
)
select * from list4 a left join list5 b on a.required_id=b.element_id