LJG
asked on
Sp_Rename -->Table --> inside a View
Question:
Can I use Sp_Rename (or similar) to rename a -->Table inside a View?
I Know:
I can go to design of the View and deal with it there or do a script Alter View
Why??:
Because it would make my job very easy if I changed a table name to just use similar SQL to below to rename the table inside the view.
SQL I use to rename a table or column inside a table or view
sp_rename @objname = 'tbl_a_Test_02' ,
@newname = 'tbl_a_Test_01' ,
@objtype = 'OBJECT'
--------------------------
sp_rename @objname = 'tbl_a_Test_01.Test_MyPay'
@newname = 'Test_Pay' ,
@objtype = 'COLUMN'
Can I use Sp_Rename (or similar) to rename a -->Table inside a View?
I Know:
I can go to design of the View and deal with it there or do a script Alter View
Why??:
Because it would make my job very easy if I changed a table name to just use similar SQL to below to rename the table inside the view.
SQL I use to rename a table or column inside a table or view
sp_rename @objname = 'tbl_a_Test_02' ,
@newname = 'tbl_a_Test_01' ,
@objtype = 'OBJECT'
--------------------------
sp_rename @objname = 'tbl_a_Test_01.Test_MyPay'
@newname = 'Test_Pay' ,
@objtype = 'COLUMN'
No....
Well, you *might* get away with it but wont help you in the end
From https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-rename-transact-sql
You might want to investigate DDL triggers on RENAME to help automate the process.... But, it will take a lot of work (you dont have inserted or deleted like a DML trigger) : https://docs.microsoft.com/en-us/sql/relational-databases/triggers/ddl-triggers
Well, you *might* get away with it but wont help you in the end
From https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-rename-transact-sql
Renaming a stored procedure, function, view, or trigger will not 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. Therefore, we recommend that sp_rename not be used to rename these object types. Instead, drop and re-create the object with its new name.
Renaming an object such as a table or column will not automatically rename references to that object. You must modify any objects that reference the renamed object manually. 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. Use sys.sql_expression_dependencies to list dependencies on the object before renaming it.
You might want to investigate DDL triggers on RENAME to help automate the process.... But, it will take a lot of work (you dont have inserted or deleted like a DML trigger) : https://docs.microsoft.com/en-us/sql/relational-databases/triggers/ddl-triggers
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the answers. I was hoping I was missing something, but I guess not.
I would like to share the points with all 3 of you, but apparently you can't do that now.
The site is going down hill.
I would like to share the points with all 3 of you, but apparently you can't do that now.
The site is going down hill.
No, but you can use ALTER VIEW repeating the current view but with the new table name.
Or DROP VIEW .. CREATE VIEW.
Not sure why this would not be considered easy. All you really need to do is use SSMS to script out the CREATE TABLE statement or go into your source control and find the object, change the CREATE to ALTER, change the table/column/whatever name, and execute. Dilly dilly.