sp_refreshView replaces view implementation when sp_rename has occured

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...

In short:

    <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>

    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 :(

    My problem is actually fixed by removing the "MyTestViewOld" <strong>but I would like to know what exactly is going on!</strong> Tnx

    Here the script to reproduce the issue! (execute in steps please)

    --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 --> 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

    Answer1:

    sp_rename is rather imperfect, and there are lots of warnings against using it on most object types:

    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.

    Whilst there isn't a specific warning about the scenario you've found, there are some clues about the potential path that is leading to the problem arising:

    Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view

    And, as you've discovered, the internal method that refreshes a view is sp_refreshsqlmodule_internal - so I'd suggest it's some form of bug down at the "module" level meta-data internals.

    You might want to consider raising the issue on SQL Server Connect, if for no other reason than for the bug to be documented elsewhere and (maybe) for them to add a further warning to the product documentation.

    Answer2:

    Encountered this problem as well. I used the following query to identify any views in the database that could cause a problem:

    select * from ( SELECT o.object_id, o.name, Replace(Replace(Replace(SUBSTRING(m.definition,13,(CHARINDEX(CHAR(13),m.definition + CHAR(13)))-13),'[',''),']',''),'dbo.','') as definitionName FROM sys.objects AS o left join sys.sql_modules AS m on o.object_id = m.object_id where o.type='v' )x where name != definitionName

    人吐槽 人点赞

Recommend

Comment

用户名: 密码:
验证码: 匿名发表

你可以使用这些语言

查看评论:sp_refreshView replaces view implementation when sp_rename has occured