Result: Caution: Changing any part of an object name could break scripts and stored procedures. If you try to rename a computed column, you’ll probably see the following error message. SQL Server doesn’t let us rename computed columns. You can use the sys.sql_expression_dependencies system catalog view to do this check. You will need to update such scripts and procedures to reference the new column name. Therefore, before you rename any columns, you should always check for scripts and stored procedures that reference that column. Now querying the view will return the correct data. We would need to update this view to reference the new column name. This view’s definition looks like this: CREATE VIEW vt1 AS This view is trying to reference c1 but that column has been renamed, and so there are no columns with that name in the table. Result: Msg 207, Level 16, State 1, Procedure vt1, Line 2Ĭould not use view or function 'vt1' because of binding errors. If we try to query a view that referenced the renamed column, we get an error. This is also true when you rename a table.ĭespite the above cautionary message, the column is renamed anyway. This is because when you rename a column, SQL Server does not automatically rename any references to that column. When you rename a column in SQL Server, you’ll probably see a message like this: Caution: Changing any part of an object name could break scripts and stored procedures. Including the Parameter NamesĪs with any stored procedure, you can also include the parameter names when calling sp_rename: EXEC = 'COLUMN' Check for References In this example, dbo is the schema name, but you will need to use whatever schema is applicable. You can also qualify the first column with the schema name, in which case, it might look something like this: EXEC sp_rename 'dbo.t1.c1', 'c11', 'COLUMN' In this case, we can use COLUMN: EXEC sp_rename 'dbo.t1.c11', 'c1', 'COLUMN' Including the Schema Name You can also include the object type as a third argument. This renames the column called c1 (in the t1 table) to c11. Here’s an example to demonstrate: EXEC sp_rename 't1.c1', 'c11' In SQL Server, you can use the sp_rename stored procedure to rename an object, including a column.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |