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'
LVL 2
LJGAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
No, you can't.  Keep in mind that SQL must parse and form an execution plan for the query or view before it runs.  So the code can't dynamically change itself, or that would invalidate the query plan.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>rename a -->Table   inside a View?
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.
0
 
Mark WillsTopic AdvisorCommented:
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 
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
0
 
LJGAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.