How to determine which Test Definitions are associated with a specified attribute?


The following query returns all Test Definitions found in all projects which is associated with the specified attribute search name. Please copy the query into a report data query by selecting "Advanced Query" in the report view.

// Borland Code
// Date: 15 April 2009

// Determine which Test Definitions are associated with a specified attribute
// Return the Test Definition, the Attribute Name and the Attribute value

// Create a new advanced query report in SCTM, copy and paste the SQL query below into the report, or
// Open SCTM database using SQL Enterprise Manager or equivalent
// Run the SQL query below against SCTM database

SELECT SCC_Projects.ProjectName AS [Project Name], TM_TestContainerName.Name AS [Test Container], TM_TestDefName.Name AS [Folder/Next Hierarchy],
TM_TestPlanNodes.Name AS [Test Definition], TM_Attributes.Name AS [Attribute Name], TM_AttributeOptions.OptionValue AS [Attribute Value]
FROM TM_AttributeOptions INNER JOIN
TM_NodeAttributeOptions ON TM_AttributeOptions.OptionID_pk = TM_NodeAttributeOptions.OptionID_pk_fk INNER JOIN
TM_TestDefinitions INNER JOIN
TM_TestPlanNodes ON TM_TestDefinitions.TestPlanNodeID_pk_fk = TM_TestPlanNodes.NodeID_pk INNER JOIN
TM_PlanTreePaths AS TM_PlanTreePaths_1 ON TM_TestPlanNodes.NodeID_pk = TM_PlanTreePaths_1.NodeID_pk_fk INNER JOIN
TM_TestContainers AS TM_TestContainers_1 ON TM_PlanTreePaths_1.ParentNodeID_pk_fk = TM_TestContainers_1.TestContainerID_pk_fk INNER JOIN
SCC_Projects ON TM_TestContainers_1.ProjectID_fk = SCC_Projects.ProjectID_pk INNER JOIN
TM_TestPlanNodes AS TM_TestDefName ON TM_TestPlanNodes.ParentNodeID_fk = TM_TestDefName.NodeID_pk INNER JOIN
TM_TestPlanNodes AS TM_TestContainerName ON TM_PlanTreePaths_1.ParentNodeID_pk_fk = TM_TestContainerName.NodeID_pk INNER JOIN
LQM_Tests ON TM_TestPlanNodes.NodeID_pk = LQM_Tests.TestID_pk INNER JOIN
TM_Attributes ON SCC_Projects.ProjectID_pk = TM_Attributes.ProjectID_pk_fk ON
TM_AttributeOptions.AttributeID_pk_fk = TM_Attributes.AttributeID_pk AND
TM_NodeAttributeOptions.NodeID_pk_fk = TM_TestPlanNodes.NodeID_pk
WHERE (TM_Attributes.Name LIKE "A%")

Note: Please specify the first letter (or first sequence of letters) of the attribute name you wish to search for in the "WHERE" clause "A%".

Find SCTM TestDefAttribute.txt
Old KB# 29712
Comment List