SQL Queries - CAST Knowledge Base - Queries on Transactions - How to list Query artifacts which are part of transactions

For CAST 8.3.36 and lower:

--Run the below query on local base 
SELECT DISTINCT 
-- if artifact involved is method, calculated transaction coverage on class instead. Applicable to all artifacts of Type Method
CASE 
  WHEN t2.objtypstr LIKE '%Method%' THEN t5.object_type_str 
  ELSE t2.objtypstr 
END artifact_type, 
CASE 
  WHEN t2.objtypstr LIKE '%Method%' THEN t5.object_fullname 
--Else t1.idkey 
END object_id 
FROM   keys t1 
       INNER JOIN objtypstr t2 
               ON t1.objtyp = t2.objtyp 
                  AND t1.objtyp IN (SELECT idtyp 
                                    FROM   typcat 
                                    WHERE  idcatparent IN (SELECT idcat 
                                                           FROM   cat 
                                                           WHERE 
                                           catnam LIKE 'APM All Artifacts') 
                                   ) 
                  AND idkey IN (SELECT child_id 
                                FROM   dss_transactiondetails) 
       INNER JOIN (SELECT DISTINCT object_id, 
                                   properties 
                   FROM   ctt_object_applications) t3 
               ON t1.idkey = t3.object_id 
                  AND t3.properties <> 1 
       INNER JOIN (SELECT DISTINCT object_id, 
                                   parent_id, 
                                   object_type, 
                                   parent_type 
                   FROM   ctt_object_parents) t4 
               ON t1.idkey = t4.object_id 
       INNER JOIN cdt_objects t5 
               ON t4.parent_id = t5.object_id; 

For CAST 8.3.37 and higher:

--Run the below query on local base 
SELECT DISTINCT 
-- if artifact involved is method, calculated transaction coverage on class instead. Applicable to all artifacts of Type Method
CASE 
  WHEN t2.objtypstr LIKE '%Method%' THEN t5.object_type_str 
  ELSE t2.objtypstr 
END artifact_type, 
CASE 
  WHEN t2.objtypstr LIKE '%Method%' THEN t5.object_fullname 
--Else t1.idkey 
END object_id 
FROM   keys t1 
       INNER JOIN objtypstr t2 
               ON t1.objtyp = t2.objtyp 
                  AND t1.objtyp IN (SELECT idtyp 
                                    FROM   typcat 
                                    WHERE  idcatparent IN (SELECT idcat 
                                                           FROM   cat 
                                                           WHERE 
                                           catnam LIKE 'APM All Artifacts') 
                                   ) 
                  AND idkey IN (SELECT child_id 
                                FROM   dss_transactiondetails) 
       INNER JOIN (SELECT DISTINCT object_id, 
                                   properties 
                   FROM   ctt_object_applications) t3 
               ON t1.idkey = t3.object_id 
                  AND t3.properties & 255 <> 1 
       INNER JOIN (SELECT DISTINCT object_id, 
                                   parent_id, 
                                   object_type, 
                                   parent_type 
                   FROM   ctt_object_parents) t4 
               ON t1.idkey = t4.object_id 
       INNER JOIN cdt_objects t5 
               ON t4.parent_id = t5.object_id; 

Enter the SQL query

Enter the SQL query