dbcc upgrade_object [ ( dbid | dbname [, ['database.[owner].]compiled_object_name' | 'check' | 'default' | 'procedure' | 'rule' | 'trigger' | 'view' [, 'force' ] ] ) ]
where:
dbid – specifies the database ID. If you do not specify dbid, all compiled objects in the current database are upgraded.
dbname – specifies the database name. If you do not specify dbname, all compiled objects in the current database are upgraded.
compiled_object_name – is the name of a specific compiled object you want to upgrade. If you use the fully qualified name, dbname and database must match, and you must enclose the fully qualified name in quotes. If the database contains more than one compiled object of the same name, use the fully qualified name. Otherwise, all objects with the same name are parsed, and if no errors are found, upgraded.
check – upgrades all check constraints and rules. Referential constraints are not compiled objects and do not require upgrading.
default – upgrades all declarative defaults and the defaults created with the create default command.
procedure – upgrades all stored procedures.
rule – upgrades all rules and check constraints.
trigger – upgrades all triggers.
view – upgrades all views.
The keywords check, default, procedure, rule, trigger, and view specify the classes of compiled objects to be upgraded. When you specify a class, all objects in that class, in the specified database, are upgraded, provided that dbcc upgrade_object finds no errors or potential problem areas.
force specifies that you want to upgrade the specified object even if it contains a select * clause. Do not use force unless you have confirmed that the select * statement will not return unexpected results. The force option does not upgrade objects that contain reserved words, contain truncated or missing source text, refer to nonexistent temporary tables, or do not match the quoted identifier setting. You must fix these objects before they can be upgraded.
If set quoted identifiers is on, use single quotes around the keywords. If set quoted identifiers is off, you can use either double quotes or single quotes.
Example 1 Upgrades all compiled objects in the active database:
dbcc upgrade_object
Example 2 Upgrades all stored procedures in the listdb database. Single quotes are used around procedure because set quoted identifiers is on:
dbcc upgrade_object(listdb, 'procedure')
Example 3 Upgrades all rules and check constraints in the listdb database. Double quotes are used around rule because set quoted identifiers is off.
dbcc upgrade_object(listdb, "rule")
Example 4 Upgrades all stored procedures named list_proc in the listdb database.
dbcc upgrade_object(listdb, list_proc)
Example 5 Upgrades the stored procedure list_proc, which is owned by the login “jkarrik”.
dbcc upgrade_object(listdb, "listdb.jkarrik.list_proc")
Example 6 Returns an error because the value of dbname is master and the value of database is listdb. These values must match.
dbcc upgrade_object(master, "listdb.jkarrik.list_proc")
Only the Database Owner or a System Administrator can execute dbcc upgrade_object. The Database Owner can upgrade his or her own objects in the database.
Upgraded objects retain the same owner that they had prior to being upgraded.