Removes existing duplicate inline default objects, converting the unique inline defaults to sharable inline default objects.
sp_merge_dup_inline_default [@report_only = {yes | no}
[, @show_progress = {yes | no}]]
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.
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.
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. =====================================================
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.
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.
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
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.”
The permission checks for sp_merge_dup_inline_default differ based on your granular permissions settings.
Granular permissions enabled |
With granular permissions enabled, you must be a user with manage database privilege. |
Granular permissions disabled |
With granular permissions disabled, you must be a user with sa_role. |
