// 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 '<issue_tracking_profile_name>%')