![]() To rename a database, requires membership in the sysadmin or dbcreator fixed server roles. To rename user types, requires CONTROL permission on the type. To rename objects, columns, and indexes, requires ALTER permission on the object. The names of most system data types and system objects can't be changed. You can change the name of an object or data type in the current database only. Refresh the metadata using sp_refreshsqlmodule or sp_refreshview. For example, if you rename a table column and that column is referenced by a non-schema-bound view or function that SELECTs all columns (using the *), the metadata for the view or function continues to reflect the original column name. Renaming a column doesn't automatically update the metadata for any objects which SELECT all columns (using the *) from that table. Use sys.sql_expression_dependencies to list dependencies on the object before renaming it. For example, if you rename a table column and that column is referenced in a trigger, you must modify the trigger to reflect the new column name. You must modify any objects that reference the renamed object manually. ![]() Renaming an object such as a table or column won't automatically rename references to that object. Instead, drop and re-create the object with its new name.Īpplies to SQL Server (all supported versions), Azure SQL Database, and Azure Synapse Analytics: Therefore, we recommend that sp_rename not be used to rename these object types. Renaming a stored procedure, function, view, or trigger won't change the name of the corresponding object either in the definition column of the sys.sql_modules catalog view or obtained using the OBJECT_DEFINITION built-in function. Sp_rename can be used to rename primary and secondary XML indexes. If a renamed index is tied to a PRIMARY KEY constraint, the PRIMARY KEY constraint is also automatically renamed by sp_rename. Sp_rename automatically renames the associated index whenever a PRIMARY KEY or UNIQUE constraint is renamed. Return code valuesĠ (success) or a nonzero number (failure) RemarksĪpplies to SQL Server (all supported versions) and Azure SQL Database: A column can only be renamed if it is a non-distribution column. It is a varchar(13) with no default value and must always be included in the sp_rename (preview) statement. In sp_rename (preview) for Azure Synapse Analytics, COLUMN is a mandatory parameter specifying that the object type to be renamed is a column. Renaming the statistics of an index automatically renames the index as well.Ī CLR user-defined type added by executing CREATE TYPE or sp_addtype. Statistics created explicitly by a user or created implicitly with an index. For example, OBJECT could be used to rename objects including constraints (CHECK, FOREIGN KEY, PRIMARY/UNIQUE KEY), user tables, and rules.Īpplies to: SQL Server 2012 (11.x) and later and Azure SQL Database. Renaming an index with statistics, also automatically renames the statistics.Īn item of a type tracked in sys.objects. This object type is required when renaming a database.Ī user-defined index. object_type is varchar(13), with a default of NULL, and can be one of these values. new_name must be a one-part name and must follow the rules for identifiers. object_name is nvarchar(776), with no default. If a fully qualified name, including a database name, is provided, the database name must be the name of the current database. ![]() Quotation marks are only necessary if a qualified object is specified. If the object to be renamed is a constraint, object_name must be in the form nstraint. If the object to be renamed is an index, object_name must be in the form table.index or. If the object to be renamed is a column in a table, object_name must be in the form lumn or. The current qualified or nonqualified name of the user object or data type. Syntax for sp_rename (preview) in Azure Synapse Analytics: sp_rename 'object_name', 'new_name' Syntax for sp_rename in SQL Server and Azure SQL Database: sp_rename 'object_name', 'new_name' We recommend you do not use this statement to rename stored procedures, triggers, user-defined functions, or views instead, drop the object and re-create it with the new name. Changing any part of an object name can break scripts and stored procedures.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |