sp_merge_dup_inline_default

Description

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

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

• 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 Adaptive 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.”