sp_copy_all_qplans

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

Syntax

sp_copy_all_qplans src_group, dest_group

Parameters

Examples

Usage

There are additional considerations when using sp_copy_all_qplans:
  • 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.

Permissions

The permission checks for sp_copy_all_qplans differ based on your granular permissions settings.

SettingDescription
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.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • 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

Related reference
sp_copy_qplan
sp_help_qpgroup