Copies all plans for one abstract plan group to another group.
sp_copy_all_qplans src_group, dest_group
sp_copy_all_qplans dev_plans, ap_stdin
The destination group must exist before you can copy plans into it. It may contain plans.
sp_copy_all_qplans calls sp_copy_qplan for each plan in the source group. Each plan is copied as a separate transaction, so any problem that keeps sp_copy_all_qplans from completing does not affect the plans that have already been copied.
sp_copy_qplan prints messages when it cannot copy a particular abstract plan. You also see these messages when running sp_copy_all_qplans.
If the query text for a plan in the destination group exactly matches the query text in the source group and the user ID is the same, the plan is not copied, and a message giving the plan ID is sent to the user, but the copying process continues with the next plan in the source group.
Copying a very large number of abstract plans can take considerable time, and also requires space on the system segment in the database and space to log the changes to the database. Use sp_spaceused to check the size of sysqueryplans, and sp_helpsegment for the system and logsegment to check the space available.
The permission checks for sp_copy_all_qplans differ based on your granular permissions settings.
| Setting | Description |
|---|---|
Enabled | With granular permissions enabled, you must be a user with manage abstract plans privilege. Any user can execute sp_copy_all_qplans to copy an abstract plan that they own. |
Disabled | With granular permissions disabled, you must be the database owner or a user with sa_role. Any user can execute sp_copy_all_qplans to copy an abstract plan that they own. |
Values in event and extrainfo columns from the sysaudits table are:
| Information | Values |
|---|---|
Event | 38 |
Audit option | exec_procedure |
Command or access audited | Execution of a procedure |
Information in extrainfo |
|