USE AdventureWorks; GO EXEC sp_rename 'Sales.SalesTerritory', 'SalesTerr'; GO
B. 重命名列 以下示例将 SalesTerritory 表中的 TerritoryID 列重命名为 TerrID。
USE AdventureWorks; GO EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN'; GO
C. 重命名索引 以下示例将 IX_ProductVendor_VendorID 索引重命名为 IX_VendorID。
USE AdventureWorks; GO EXEC sp_rename N'Purchasing.ProductVendor.IX_ProductVendor_VendorID', N'IX_VendorID', N'INDEX'; GO
D. 重命名别名数据类型 以下示例将 Phone 别名数据类型重命名为 Telephone。
USE AdventureWorks; GO EXEC sp_rename N'Phone', N'Telephone', N'USERDATATYPE'; GO
存储过程代码
USE[master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTERprocedure[sys].[sp_rename] @objnamenvarchar(1035), -- up to 4-part "old" name @newname sysname, -- one-part new name @objtypevarchar(13) =null-- identifying the name as /**//* DOCUMENTATION: [1] To rename a table, the @objname (meaning OldName) parm can be passed in totally unqualified or fully qualified. [2] The SA or DBO can rename objects owned by lesser users, without the need for SetUser. [3] The Owner portion of a qualified name can usually be passed in in the omitted form (as in MyDb..MyTab or MyTab). The typical exception is when the SA/DBO is trying to rename a table where the @objname is present twice in sysobjects as a table owned only by two different lesser users; requiring an explicit owner qualifier in @objname. [4] An unspecified Owner qualifier will default to the current user if doing so will either resolve what would otherwise be an ambiguity within @objtype, or will result in exactly one match. [5] If Database is part of the qualified @objname, then it must match the current database. The @newname parm can never be qualified. [6] Here are the valid @objtype values. They correspond to system tables which track each type: 'column' 'database' 'index' 'object' 'userdatatype' The @objtype parm is sometimes required. It is always required for databases. It is required whenever ambiguities would otherwise exist. Explicit use of @objtype is always encouraged. [7] Parms can use quoted_identifiers. For example: Execute sp_rename 'amy."his table"','"her table"','object' */ set nocount on set ansi_padding on declare@objtypeINvarchar(13), @CurrentDb sysname, @CountNumNodesint, @UnqualOldName sysname, @QualName1 sysname, @QualName2 sysname, @QualName3 sysname, @OwnAndObjNamenvarchar(517), -- "[owner].[object]" @SchemaAndTypeNamenvarchar(517), -- "[schema].[type]" @objidint, @xtypenchar(2), @indidint, @colidint, @cnstidint, @xusertypeint, @schidint, @objid_tmpint, @xtype_tmpnchar(2), @retcodeint, @publishedbit-- Indicates table is used in replication -- initial (non-null) settings select@CurrentDb=db_name(), @objtypeIN=@objtype -- make type case insensitive select@objtype=lower(@objtypeIN collate Latin1_General_CI_AS) ------------------------------------------------------------------------ ------------------- PHASE 10: Simple parm edits --------------------- ------------------------------------------------------------------------ -- Valid rename-type param? if (@objtypeisnotnullAND @objtypenotin ('column', 'database', 'index', 'object', 'userdatatype')) begin raiserror(15249,-1,-1,@objtypeIN) return1 end -- null names? if (@newnameISnull) begin raiserror(15223,-1,11,'NewName') return1 end if (@objnameISnull) begin raiserror(15223,-1,-1,'OldName') return1 end -- Is NewName minimally valid? exec@retcode= sp_validname @newname if@retcode<>0 begin raiserror(15224,-1,15,@newname) return1 end -- Parse apart the perhaps dots-qualified old name. select@UnqualOldName=parsename(@objname, 1), @QualName1=parsename(@objname, 2), @QualName2=parsename(@objname, 3), @QualName3=parsename(@objname, 4) if (@UnqualOldNameISNull) begin raiserror(15253,-1,-1,@objname) return1 end -- count name parts -- select@CountNumNodes= (case when@QualName3isnotnullthen4 when@QualName2isnotnullthen3 when@QualName1isnotnullthen2 else1 end) if (@objtype='database'AND@CountNumNodes>1) begin raiserror(15395,-1,20,@objtypeIN) return1 end if (@objtypein ('object','userdatatype') AND@CountNumNodes>3) begin raiserror(15225,-1,-1,@objname, @CurrentDb, @objtypeIN) return1 end --------------------------------------------------------------------------- ---------------------- PHASE 20: Settle Parm1ItemType ------------------ --------------------------------------------------------------------------- ------------- database? if (@objtype='database') begin execute@retcode= sys.sp_renamedb @UnqualOldName ,@newname-- de-docu old sproc if@retcode<>0 return1 return0 end BEGINTRANSACTION ------------ type? if (@objtype='userdatatype'or@objtypeINisnull) begin select@xusertype= user_type_id, @schid= schema_id from sys.types where user_type_id = type_id(@objname) -- Lock scalar type exclusively and check permissions ifnot (@xusertypeisnull) begin -- cannot pass @objname as it is nvarchar(1035) and Invoke fails %%ScalarType does not compile with four part name. -- cross db type resolution is not allowed (already taken care of by type_id) so only two part name is required. if (@QualName1isnotnull) select@SchemaAndTypeName=QuoteName(@QualName1) +'.'+QuoteName(@UnqualOldName) else select@SchemaAndTypeName=QuoteName(@UnqualOldName) EXEC%%ScalarType(MultiName =@SchemaAndTypeName).LockMatchID(ID =@xusertype, Exclusive =1) if (@@error<>0) select@xusertype=null end -- check for wrong param if ((@xusertypeisnotnullAND@objtype<>'userdatatype') OR (@xusertypeisnullAND@objtype='userdatatype')) begin COMMITTRANSACTION raiserror(15248,-1,-1,@objtypeIN) return1 end ifnot (@xusertypeisnull) select@objtype='userdatatype' end -- assuming column/index-name, obtain object/column id's if (@objtypein ('column', 'index') or@objtypeINisnull) begin if@QualName2isnotnull select@objid=object_id(QuoteName(@QualName2) +'.'+QuoteName(@QualName1)) else select@objid=object_id(QuoteName(@QualName1)) ifnot (@objidisnull) -- nice try? begin -- obtain owner-qual object name select@schid=ObjectProperty(@objid, 'schemaid') select@OwnAndObjName=QuoteName(schema_name(@schid))+'.'+QuoteName(object_name(@objid)) EXEC%%Object(MultiName =@OwnAndObjName).LockMatchID(ID =@objid, Exclusive =1, BindInternal =0) if@@error<>0 select@objid=null else select@xtype= type from sys.objects whereobject_id=@objid end end ------------ column? if (@objtype='column'or@objtypeINisnull) begin -- find column if (@xtypein ('U','V')) select@colid= column_id from sys.columns whereobject_id=@objidand name =@UnqualOldName -- check for wrong param if ((@colidisnotnullAND@objtype<>'column') OR (@colidisnullAND@objtype='column')) begin COMMITTRANSACTION raiserror(15248,-1,-1,@objtypeIN) return1 end -- remember if we've found a column ifnot (@colidisnull) begin select@published= is_published from sys.objects whereobject_id=@objid if@published=0 select@published= is_merge_published from sys.tables whereobject_id=@objid if (@published<>0) begin COMMITTRANSACTION raiserror(15051,-1,-1) return (0) end select@objtype='column' end end ------------ index? if (@objtype='index'or@objtypeINisnull) begin -- find index if (@xtypein ('U','V')) select@indid= stats_id from sys.stats whereobject_id=@objidand name =@UnqualOldName -- if we could not find a regular index, try finding an xml index if (@indidisnull ) select@indid=object_idfrom sys.xml_indexes whereobject_id=@objidand name =@UnqualOldName -- check for wrong param if ((@indidisnotnullAND@objtype<>'index') OR (@indidisnullAND@objtype='index')) begin COMMITTRANSACTION raiserror(15248,-1,-1,@objtypeIN) return1 end ifnot (@indidisnull) begin select@objtype='index' select@cnstid=object_id, @xtype= type from sys.objects where name =@UnqualOldNameand parent_object_id =@objidand type in ('PK','UQ') end end ------------ object? if (@objtype='object'or@objtypeINisnull) begin -- get object id, type select@objid_tmp=object_id(@objname) ifnot (@objid_tmpisnull) begin -- obtain owner-qual object name select@schid=ObjectProperty(@objid_tmp, 'schemaid') select@OwnAndObjName=QuoteName(schema_name(@schid))+'.'+QuoteName(object_name(@objid_tmp)) EXEC%%Object(MultiName =@OwnAndObjName).LockMatchID(ID =@objid_tmp, Exclusive =1, BindInternal =0) if@@error<>0 select@objid_tmp=null end select@xtype_tmp= type from sys.objects whereobject_id=@objid_tmp -- if object is a system table, a Scalar function, or a table valued function, skip it. -- Cannot rename system table if@xtype_tmp='S' select@objid_tmp=NULL -- Locks parent object and increments schema_ver ifnot (@objid_tmpisnull) begin if (@xtype_tmpin ('U')) begin select@published= is_merge_published | is_published from sys.tables whereobject_id=@objid_tmp if (@published<>0) begin COMMITTRANSACTION raiserror(15051,-1,-1) return (0) end end -- parent already locked via locking object end -- check for wrong param if ((@objid_tmpisnotnullAND@objtype<>'object') OR (@objid_tmpisnullAND@objtype='object')) begin COMMITTRANSACTION raiserror(15248,-1,-1,@objtypeIN) return1 end ifnot (@objid_tmpisnull) select@objtype='object', @objid=@objid_tmp, @xtype=@xtype_tmp end --------------------------------------------------------------------- ------------------- PHASE 30: More parm edits -------------------- --------------------------------------------------------------------- -- item type determined? if (@objtypeisnull) begin COMMITTRANSACTION raiserror(15225,-1,-1,@objname, @CurrentDb, @objtypeIN) return1 end -- was the original name valid given this type? if (@objtypein ('object','userdatatype') AND@CountNumNodes>3) begin COMMITTRANSACTION raiserror(15225,-1,-1,@objname, @CurrentDb, @objtypeIN) return1 end -- verify db qualifier is current db if (@objtypein ('object','userdatatype')) select@QualName3=@QualName2 if (isnull(@QualName3, @CurrentDb) <>@CurrentDb) begin COMMITTRANSACTION raiserror(15333,-1,-1,@QualName3) return1 end if (@objtype<>'userdatatype') begin -- check if system object select@schid=ObjectProperty(@objid, 'schemaid') if (ObjectProperty(@objid, 'IsMSShipped') =1OR ObjectProperty(@objid, 'IsSystemTable') =1) begin COMMITTRANSACTION raiserror(15001,-1,-1, @objname) return1 end end -- System created Date Correlation view and its columns/indexes cannot be renamed. if (@objidisnotnull AND@xtype='V ' AND1=Isnull((select is_date_correlation_view from sys.views whereobject_id=@objid), 0) ) begin COMMITTRANSACTION raiserror(15168,-1,-1, @objname) return1 end -- make sure orig no longer shows null if@objtypeINisnull select@objtypeIN=@objtype -- Check for name clashing with existing name(s) if (@newname<>@UnqualOldName) begin -- column name clash? if (@objtype='column') if (ColumnProperty(@objid, @newname, 'isidentity') isnotnull) select@UnqualOldName=NULL -- index name clash? if ( (@objtype='object'AND@xtypein ('PK','UQ')) OR@objtype='index') ifexists (select*from sys.stats whereobject_id=@objidand name =@newname) select@UnqualOldName=NULL -- cnst name clash? if (@objtype='object'OR@cnstidISNOTnull) if (object_id(QuoteName(schema_name(@schid)) +'.'+QuoteName(@newname), 'local') isnotnull) select@UnqualOldName=NULL -- stop on clash if (@UnqualOldNameisnull) begin COMMITTRANSACTION raiserror(15335,-1,-1,@newname,@objtypeIN) return1 end end -------------------------------------------------------------------------- -------------------- PHASE 32: Temporay Table Isssue ------------------- -------------------------------------------------------------------------- -- Disallow renaming object to or from a temp name (starts with #) if (@objtype='object'AND (substring(@newname,1,1) = N'#'OR substring(object_name(@objid),1,1) = N'#')) begin COMMITTRANSACTION raiserror(15600,-1,-1, 'sys.sp_rename') return1 end -------------------------------------------------------------------------- -------------------- PHASE 34: Cautionary messages -------------------- -------------------------------------------------------------------------- if@objtype='column' begin -- Check for Dependencies: No column rename if enforced dependency on column ifexists (select*from sys.sql_dependencies d where d.referenced_major_id =@objid AND d.referenced_minor_id =@colid AND d.class =1 -- filter dependencies where the dependent object d.object_id is a date correlation view. -- Date correlation view owner is the same as of any of the tables they are dependent upon so user having alter access -- to any of the participating tables will have catalog view access to the MPIV for the following query to succeed. AND0=Isnull((select is_date_correlation_view from sys.views o where o.object_id= d.object_id), 0) ) begin COMMITTRANSACTION raiserror(15336,-1,-1, @objname) return1 end end elseif@objtype='object' begin -- Check for Dependencies: No RENAME or CHANGEOWNER of OBJECT when exists: ifexists (select*from sys.sql_dependencies d where d.referenced_major_id =@objid-- A dependency on this object AND d.class >0-- that is enforced AND@objid<> d.object_id-- that isn't a self-reference (self-references don't use object name) -- filter dependencies where the dependent object d.object_id is a date correlation view. AND0=Isnull((select is_date_correlation_view from sys.views o where o.object_id= d.object_id), 0) -- And isn't a reference from a child object (also don't use object name) -- As we might not have permission on the dependent object, we list all the child -- objects of the object to be renamed and make sure that child object id is not the depending object here. AND0= (selectcount(*) from sys.objects o where o.parent_object_id =@objidand o.object_id= d.object_id) ) begin COMMITTRANSACTION raiserror(15336,-1,-1, @objname) return1 end end -- WITH DEFERRED RESOLUTION, sql_dependencies IS NOT VERY ACCURATE, SO WE ALSO -- RAISE THIS WARNING **UNCONDITIONALLY**, EVEN FOR NON-OBJECT RENAMES raiserror(15477,-1,-1) -- warn about dependencies... if (@objtype='objects' andexists (select*from sys.sql_dependencies d where d.referenced_major_id =@objid -- filter dependencies where the dependent object d.object_id is a date correlation view. AND0=Isnull((select is_date_correlation_view from sys.views o where o.object_id= d.object_id), 0) )) raiserror(15337,-1,-1) -------------------------------------------------------------------------- --------------------- PHASE 40: Update system tables ------------------ -------------------------------------------------------------------------- -- DO THE UPDATES -- if (@objtype='userdatatype') -------- change type name begin EXEC%%ScalarType(ID =@xusertype).SetName(Name =@newname) if@@error<>0 begin COMMITTRANSACTION raiserror(15335,-1,-1,@newname,@objtypeIN) return1 end end elseif (@objtype='object') -------- change object name begin -- update the object name EXEC%%Object(ID =@objid).SetName(Name =@newname) if@@error<>0 begin COMMITTRANSACTION raiserror(15335,-1,-1,@newname,@objtypeIN) return1 end end elseif (@objtype='index') -------- change index name begin -- update the index name, and change object name if cnst EXEC%%IndexOrStats(ObjectID =@objid, Name =@UnqualOldName).SetName(Name =@newname) if@@error<>0 begin COMMITTRANSACTION raiserror(15335,-1,-1,@newname,@objtypeIN) return1 end end elseif (@objtype='column') -------- change column name begin -- Use DBCC to check for column in use by check-constraint, computed-column, etc -- THIS IS NOT A DOCUMENTED DBCC: DO NOT USE DIRECTLY! DBCC RENAMECOLUMN ( @OwnAndObjName, @UnqualOldName, @newname ) end COMMITTRANSACTION -- Success -- return0-- sp_rename