How can I recursive check sp_refreshsqlmodule in sql server?

Question

How can I recursive check sp_refreshsqlmodule in sql server?

ram1447

SSC-Addicted

Points: 499
More actions
January 17, 2016 at 12:01 am

#314945

Hi all
How can I recursive check sp_refreshsqlmodule in sql server?
Sample:
1- I create one table:
CREATE TABLE [dbo].[L1](
[ID] [int] NULL,
[Name] [nchar](10) NULL,
[Family] [nchar](10) NULL
) ON [PRIMARY]
2-I create first view:
CREATE VIEW [dbo].[L2]
AS
SELECT Name, Family, ID
FROM dbo.L1
3-I create second View use with first view
create view [dbo].[L3]
as
select Name + ' ' + Family Fullname
from [dbo].[L2]
GO
4-Then I change column name in table L1
EXECUTE sp_rename N'dbo.L1.Name', N'Name1', 'COLUMN'
5-I check first view: The result is pleasant because I change column name
EXEC sp_refreshsqlmodule '[dbo].[L2]'
--Msg 207, Level 16, State 1, Procedure sp_refreshsqlmodule_internal, Line 71
--Invalid column name 'Name'.
6-But when I check second view: It is not pleasant
EXEC sp_refreshsqlmodule '[dbo].[L3]'
--Command(s) completed successfully.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

I have a MUCH better idea: Use WITH SCHEMABINDING on EVERY object you ever create in SQL Server where it is allowed. I hope you will find that this will prevent you from doing the BAD developer mistake you are doing, which is not doing a complete impact analysis when you change a column name.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 2

Change the column name to a column name present in one of the tables referenced in the query.

MMartin1 One Orange Chip Points: 27795 More actions · Answer 3

How would you know in the real world that [dbo].[L2] was the first view in the chain, where you started the refresh? Certainly things are not named this way in production environments. Renaming columns ideally requires a meeting of the mind of the team to determine impact and really if it is something worth doing in the end.

----------------------------------------------------

MMartin1 One Orange Chip Points: 27795 More actions · Answer 4

Another thing to consider is avoiding the chaining of views if possible. You keep the compiler from getting a good query plan. Things are not as re-usable in the SQL world as they would be in the world of procedural programming. When all views have one degree of separation from the source you can see them more readily

https://msdn.microsoft.com/en-us/library/ms190624(v=sql.120).aspx

----------------------------------------------------