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

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