Copying all plans in a group

sp_copy_all_qplans copies all of the plans in one abstract plan group to another group. This example copies all of the plans from the test_plans group to the helpful_plans group:

sp_copy_all_qplans test_plans, helpful_plans

The helpful_plans group must exist before you execute sp_copy_all_qplans. It can contain other plans.

sp_copy_all_qplans copies each plan in the group by executing sp_copy_qplan, so copying a plan may fail for the same reasons that sp_copy_qplan might fail. See “Comparing two abstract plans”.

Each plan is copied as a separate transaction, and failure to copy any single plan does not cause sp_copy_all_qplans to fail. If sp_copy_all_qplans fails for any reason, and has to be restarted, you see a set of messages for the plans that have already been successfully copied, telling you that they exist in the destination group.

A new plan ID is assigned to each copied plan. The copied plans have the original user’s ID. To copy abstract plans and assign new user IDs, you must use sp_export_qpgroup and sp_import_qpgroup. See “Importing and exporting groups of plans”.

A System Administrator or Database Owner can copy all plans in the database. Other users can copy only plans that they own.