sp_refreshView replaces view implementation when s

问题:

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:

• I have a view (MyTestView).
• This view is renamed (MyTestViewOld).
• A new view is created with the original name of the renamed view AND a different select query (MyTestView).
• An sp_refreshview is executed on the renamed view (MyTestViewOld).
• Now the implementation of MyTestView is that of MyTestViewOld!

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" but I would like to know what exactly is going on! 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', '[e-mail]')
insert dbo.MyTestContacts values ('Mr', 'Valens', 'Ritchie', '987654312', '[e-mail]')
insert dbo.MyTestContacts values ('Mr', 'Richardson', 'Jiles Perry', '987654312', '[e-mail]')


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

回答1:

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.

回答2:

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