Databases
StackOverflow2013
Postsdbo
POsp_refreshView replaces view implementation when sp_rename has occured
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POsp_refreshView replaces view implementation when sp_rename has occured
    primarykey
    data
    text
    <p>I have a very weird issue with a view which I can reproduce. I'm quite sure that some view metadata is not up to date and causes this issue... </p> <p>In short:</p> <ul> <li>I have a view (MyTestView).</li> <li>This view is renamed (MyTestViewOld).</li> <li>A new view is created with the original name of the renamed view AND a different select query (MyTestView). </li> <li>An sp_refreshview is executed on the renamed view (MyTestViewOld). </li> <li>Now the implementation of MyTestView is that of MyTestViewOld!</li> </ul> <p>I tried looking into the SP sp_refreshview, but he just executed another SP called sp_refreshsqlmodule_internal. I don't see this view in the system stored procedures of the master database :(</p> <p>My problem is actually fixed by removing the "MyTestViewOld" <strong>but I would like to know what exactly is going on!</strong> Tnx</p> <p>Here the script to reproduce the issue! (execute in steps please)</p> <pre><code>--create a test table create table dbo.MyTestContacts( [Title] [varchar](20) NULL, [Name] [varchar](255) NULL, [FirstName] [varchar](255) NULL, [Telephone] [varchar](50) NULL, [Email] [varchar](255) NULL ) --insert data in the temp table insert dbo.MyTestContacts values ('Mr', 'Holly', 'Buddy', '0123456798', 'buddy@holly.co') insert dbo.MyTestContacts values ('Mr', 'Valens', 'Ritchie', '987654312', 'ritchie@valens.co') insert dbo.MyTestContacts values ('Mr', 'Richardson', 'Jiles Perry', '987654312', 'jp@richardson.co') --create a view SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create view dbo.MyTestView as select Title, Name, FirstName from dbo.MyTestContacts GO --do a SCRIPT VIEW AS / ALTER TO in sql management studio to verify the view implementation --rename the view exec sp_rename 'dbo.MyTestView', 'MyTestViewOld' --BIS1 (explained below; used in a 2nd test run) --create a view with the same name as the first view, but different implementation! SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create view dbo.MyTestView as select Title, Name, FirstName, Telephone from dbo.MyTestContacts GO --do a SCRIPT VIEW AS / ALTER TO in sql management studio to verify the view implementation of MyTestView (telephone is added) --perform a refreshView action on the MyTestViewOld view exec sp_refreshview 'dbo.MyTestViewOld' --do a SCRIPT VIEW AS / ALTER TO in sql management studio to verify the view implementation of MyTestView --&gt; THIS SHOWS THE MyTestViewOld IMPLEMENTATION!!!! --(BIS1) Note that this sequence gets broken when I do an ALTER VIEW on MyTestViewOld right after the rename! --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --alter view dbo.MyTestViewOld as --select FirstName, Name, Email, Telephone from dbo.MyTestContacts --GO --drop everything drop view dbo.MyTestViewOld drop view dbo.MyTestView drop table dbo.MyTestContacts </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USa_horse_with_no_name
    1. USdeblendewim
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. CO[`sp_rename`](http://msdn.microsoft.com/en-us/library/ms188351.aspx): "Changing any part of an object name can break scripts and stored procedures. 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."
      singulars
      1. POsp_refreshView replaces view implementation when sp_rename has occured
      1. USDamien_The_Unbeliever
    2. CO@deblendewim - interesting, but there is no real reason to call sp_rename on a view, just drop and recreate.
      singulars
      1. POsp_refreshView replaces view implementation when sp_rename has occured
      1. USEkoostikMartin
    3. COI completely follow your statement! Even management studio warns me about this action: "Caution: Changing any part of an object name could break scripts and stored procedures.". But this still doesn't answer my question which is: what is going on? Maybe my question is too far fetched because it's happening in the internal SQL Server's brain :)
      singulars
      1. POsp_refreshView replaces view implementation when sp_rename has occured
      1. USdeblendewim
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload