sp_merge_dup_inline

Removes existing duplicate inline default objects, converting the unique inline defaults to sharable inline default objects.

Syntax

sp_merge_dup_inline_default [@report_only = {yes | no} 
	[, @show_progress = {yes | no}]]

Parameters

@report_only – reports the number of unique inline defaults in the current database but performs no changes if you specify yes. If you specify no:
- sp_merge_dup_inline_default removes duplicate inline defaults, and all unique inline defaults are changed to sharable inline defaults
- Existing column definitions referencing the duplicate inline defaults are updated to reference the sharable inline defaults
The default value for @report_only is yes.
@show_progress – if set to yes, sp_merge_dup_inline_default displays hashmarks to show progress when @report_only is set to no.
The default value for @show_progress is no.

Examples

Example 1 – Runs sp_merge_dup_inline_default against the pubs2 database without any options. sp_merge_dup_inline_default makes no changes, but displays an informational message indicating the approximate number of unique inline defaults:

sp_merge_dup_inline_default

=====================================================
sp_merge_dup_inline_default is used to identify duplicate inline default objects, 
subsequently to convert one of them into sharable inline default object and remove the 
rest. As the result, it will remove entries from sysobjects, syscomments and 
sysprocedures. It will also update entries in syscolumns, syscomments and sysprocedures. 

Following is the current state of your inline default objects found out by 
sp_merge_dup_inline_default and what it could potentially do to them. By default, 
sp_merge_dup_inline_default only reports the current state and this warning message. If 
you really intend to carry out the changes, please rerun this stored procedure using 

sp_merge_dup_inline_default @report_only = "NO"

Database pubs2 has about 0 unique inline defaults If you convert them into sharable inline 
defaults, the rest of total 0 duplicate defaults can be removed from the system catalogs.
=====================================================

Example 2 – Converts the unique inline default to shareable inline defaults:

sp_merge_dup_inline_default @report_only = 'NO'

Total 2 duplicate defaults are removed and 7 defaults are converted to sharable inline
defaults. Database is modified and in single-user mode. System Administrator (SA) must 
reset it to multi-user mode with sp_dboption.

Example 3 – Produces the following output if there are no duplicate inline defaults:

sp_merge_dup_inline_default @report_only = 'NO'

Database is not modified. Please try it later if duplicate inline defaults do exist and
the current resource limitation is preventing this conversion process.

Example 4 – Includes the show_progress parameter to indicate progress:

sp_merge_dup_inline_default @report_only = 'NO', @show_progress = "YES"

Calculating...
Converting...
[#                                                                ]
[##########                                                       ]
[################### ]
[############################ ]
[##################################### ]
[############################################## ]
[################################################### ]
[####################################################]

Total 2 duplicate defaults are removed and 7 defaults
are converted to sharable inline defaults.
Database is modified and in single-user mode.
System Administrator (SA) must reset it to multi-user mode with sp_dboption

Usage

There are additional considerations when using sp_merge_dup_inline_default:

You cannot run sp_merge_dup_inline_default on system databases.
User databases must be in single-user mode before you run sp_merge_dup_inline_default.
You may re-run sp_merge_dup_inline_default if the system procedure aborts.
If sp_merge_dup_inline_default issues an error message stating that the SAP ASE server is out of locks:
- Increase the value for number of locks, or
- Reduce the lock promotion threshold with sp_setpglockpromote or sp_setrowlockpromote.
Re-run sp_merge_dup_inline_default, and reset the values after sp_merge_dup_inline_default finishes.
sp_merge_dup_inline_default changes only inline default objects for which the default value is a literal string constant or simple numbers (the literal string constant cannot include escaped string delimiters).
sp_merge_dup_inline_default does not remove any duplicate inline default objects if their source text in syscomments is “encrypted.”

Permissions

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

Setting	Description
Enabled	With granular permissions enabled, you must be a user with `manage database` privilege.
Disabled	With granular permissions disabled, you must be a user with sa_role.