sp_copy_qplan

Copies one abstract plan to an abstract plan group.

Syntax

sp_copy_qplan src_id, dest_group

Parameters

Examples

Usage

There are additional considerations when using sp_copy_qplan:
  • The destination group must exist before you can copy an abstract plan into it. You do not need to specify a source group, since plans are uniquely identified by the plan ID.

  • A new plan ID is generated when the plan is copied. The plan retains the ID of the user who created it, even if the system administrator or database owner copies the plan. To assign a different user ID, a system administrator or database owner can use sp_export_qpgroup and sp_import_qpgroup.

  • If the query text for a plan in the destination group exactly matches the query text in the source group and the user ID, the plan is not copied, and a message giving the plan IDs is sent to the user.

  • To copy all of the plans in an abstract plan group, use sp_copy_all_qplans.

Permissions

The permission checks for sp_copy_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 execute sp_copy_qplan to copy a plan that they own.

Disabled

With granular permissions disabled, you must be the database owner or a user with sa_role.

Any user execute sp_copy_qplan to copy a 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_all_qplans
sp_help_qpgroup
sp_help_qplan
sp_import_qpgroup