Copies all plans for one abstract plan group to another group.
sp_copy_all_qplans src_group, dest_group
is the name of the source abstract plan 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.
Any user can execute sp_copy_all_qplans to copy an abstract plan that he or she owns. Only the System Administrator or Database Owner can copy plans that are owned by other users.
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 |
|
System procedures sp_copy_qplan, sp_help_qpgroup
