Problem:

How can I determine if there are Test Definitions associated with an Issue Tracking Profile?

Resolution:

The following article details steps that must be performed before it is possible to delete an Issue Tracking Profile
Solution: How can I delete an issue tracking profile from Issue Manager? When I try to delete the issue profile, I get the error that it is in use.

At times it may be difficult and time consuming to manually search for all Test Definitions which have issues associated with an Issue Tracking Profile, therefore, using the SQL statement below, users can execute this against their SCTM database to determine all Test Definitions, if any is associated with an Issue Tracking Profile.

// Borland Code VOzohili
// Date: 03 July 2009

// Deletion of Issue Tracking Profile(s) associated to a Test Definition may result in an error
// that the profile is still in use. Use the SQL query below to determine which Test Definition
// is still associated with the Issue Tracking profile

// Open SCTM database using SQL Enterprise Manager or equivalent
// Run the SQL query below against SCTM database


SELECT SCC_IssueTrackingProfiles.Name AS [Issue Tracking Profile], SCC_Projects.ProjectName AS [Project Name],
TM_TestPlanNodes_1.Name AS [Test Container Name], TM_TestPlanNodes.Name AS [Test Definition Name]
FROM TM_TestDefinitions INNER JOIN
SCC_IssueTrackingProfiles INNER JOIN
TM_Issues ON SCC_IssueTrackingProfiles.ProfileID_pk = TM_Issues.ProfileID_fk INNER JOIN
TM_IssuesHistory ON TM_Issues.IssueID_pk = TM_IssuesHistory.IssueID_fk ON
TM_TestDefinitions.TestPlanNodeID_pk_fk = TM_Issues.NodeID_fk INNER JOIN
TM_TestPlanNodes ON TM_TestDefinitions.TestPlanNodeID_pk_fk = TM_TestPlanNodes.NodeID_pk INNER JOIN
TM_TestContainers ON TM_TestPlanNodes.ParentNodeID_fk = TM_TestContainers.TestContainerID_pk_fk INNER JOIN
TM_TestPlanNodes AS TM_TestPlanNodes_1 ON TM_TestContainers.TestContainerID_pk_fk = TM_TestPlanNodes_1.NodeID_pk INNER JOIN
SCC_Projects ON TM_TestContainers.ProjectID_fk = SCC_Projects.ProjectID_pk
WHERE (SCC_IssueTrackingProfiles.Name LIKE "%")

Note: Please specify the Issue Tracking Profile name in the where clause above to replace