Find Test Cases Not in a Plan

Question

Hi,

We need to know if there are test cases not assigned to a plan.  For example, if I have hundreds of test cases that have been used in a prior plan and now I assign some of them to a new plan. How do I go back and confirm that all the test cases I wanted to assign to my new plan are actually assigned to a plan. Current, we bring up a query listing the test cases identified for the new plan, then visually compare that listing with the test cases shown in the plan: a very painful exercise and prone to error.

I need a SQL query or code that I can use to show me for specified team project, area path and iteration path, which test cases are not assigned to a specific plan and which test cases are not assigned to any plan.

Thanks, Bob

---

Bob Hardister

Answer 1

Hello Bob,

I think it is possible for you to get all the test cases from a specific test plan via TFS API, and then you can store them in a list. When you would like to add test cases to the new test plan next time, you can first check out the list to see if the test case you would like to add is in that list.

For further information about how to use TFS API to get all test cases from a specific test plan, see:

http://blogs.microsoft.co.il/blogs/shair/archive/2010/07/06/tfs-api-part-27-test-plans-test-suites-test-cases-mapping.aspx

Thanks.

---

Vicky Song [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Have a look at this SQL snippet... maybe you can rework it into what you want. If you let @ParamTestSuite be the id of the root suite in a test plan this query will give you all test suites in the plan and all test cases in each suite.

;WITH SuiteCTE
  AS
  (
  SELECT SuiteId, ParentSuiteId
   FROM tbl_Suite
   WHERE SuiteId = @ParamTestSuite
   UNION ALL
   SELECT S.SuiteId, S.ParentSuiteId
   FROM tbl_Suite AS S
   JOIN SuiteCTE AS C
     ON S.ParentSuiteId = C.SuiteId
   )
select distinct tse.SuiteId, TestCaseId 
from SuiteCTE
join tbl_SuiteEntry tse on SuiteCTE.SuiteId = tse.SuiteId
where TestCaseId <> 0

---

Jesper Fernström
Transcendent Group