sp_copy_all_qplans

Copies all plans for one abstract plan group to another group.

sp_copy_all_qplans src_group, dest_group

dest_group: is the name of the abstract plan group to which the plans are to be copied.

Copies all of the abstract plans in the dev_plans group to the ap_stdin 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.

Granular permissions 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.

Granular permissions 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:

Event	Audit option	Command or access audited	Information in extrainfo
38	exec_procedure	Execution of a procedure	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect