July 19, 2010 at 10:10 am
In the code below, the function continues to return the columns from the original view after the view is altered. The only way I found to "fix" this is to execute sp_refreshsqlmodule. However, I need to run this for any Function or Stored Procedure that references the view. Is there a more elegant way to keep the function(s) in sync with view changes.
If object_id('TestTable') is Not Null Drop Table TestTable
If Object_id('TestView') is Not Null Drop View TestView
If Object_id('TestFunction') is Not Null Drop Function TestFunction
Create Table TestTable(EmployeeID Int,FName varchar(20),LName varchar(20))
Insert TestTable values(1,'John' ,'Smith')
Insert TestTable values(2,'Tom' ,'Jones')
Insert TestTable values(1,'Sally','Smith')
/* Initial View with 2 columns---*/
Go
Create View TestView as
Select
EmployeeID,FName
From TestTable
Go
Create Function TestFunction()
Returns Table
As
Return
(
Select * from TestView
)
Go
Select 'Before Alter',* from testfunction()
/* Modified View with 3 columns---*/
Go
Alter View TestView as
Select
EmployeeID,FName,LName
From TestTable
Go
Select 'Alter Alter',* from testfunction()
July 19, 2010 at 10:58 am
Simple answer:
There is no "more elegant way". You can script executing of sp_refreshsqlmodule for each UDF in your database and run this script after altering any view/table (please note that sp_refreshsqlmodule does not work for schemabound functions...).
BTW. It is generaly a bad practicve to do SELECT *...
You can add SCHEMABINDING option in your function. It will stop anyone altering objects referred in function without dropping function first. I know it is not what you really want, but at least it will insure your functions integrity...
Just a sample of script to refresh all non-schemabound functions:
DECLARE @sql NVARCHAR(1000)
DECLARE myFoos CURSOR FAST_FORWARD
FOR
SELECT 'EXEC sys.sp_refreshsqlmodule ''' + ROUTINE_SCHEMA + '.' + ROUTINE_NAME + ''''
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION' AND ROUTINE_DEFINITION NOT LIKE '%SCHEMABINDING%'
OPEN myFoos
FETCH NEXT FROM myFoos INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @sql
EXEC sp_executesql @sql
FETCH NEXT FROM myFoos INTO @sql
END
CLOSE myFoos
DEALLOCATE myFoos
July 19, 2010 at 1:29 pm
I wrote a tool for this type of dependency refresh problem
it calculates the dependency graph for a group of changing objects
and generates the correct dependency-aware script to refresh the objects
it detects schemabound objects and correctly removes the schemabinding
after the objects are changed it replaces the schemabinding and recreates
any indexes (on views) that were silently dropped during the alter
because it performs all the operations in the correct order the fragile sys.depends in SQL 2005
stays intact (normally it deletes all child dependency records when a parent changes)
furthermore this approach avoids the nasty SQL problem of intermediate execution plans
being cached with stale column indices - this can result in columns jumping:
select [name], [address]
can end up returning
[name], [some other column]
if you alter objects in the wrong order
I have been curious for years how other SQL developers solve these issues - when I ask them
they just look blank ...
July 19, 2010 at 2:48 pm
doobya (7/19/2010)
...I have been curious for years how other SQL developers solve these issues - when I ask them
they just look blank ...
Other SQL developers may be not generaly using "SELECT * ..." in such situations...
July 19, 2010 at 4:00 pm
Eugene Elutin (7/19/2010)
Other SQL developers may be not generaly using "SELECT * ..." in such situations...
Huh? I don't mean SELECT *
I mean solve the general dependency / refresh problem in SQL Server
especially in the case of hot / online schema changes
OP's issue wasn't the * it was refreshing the view (and dependent objects)
(The refresh idea above won't work properly in many cases)
July 19, 2010 at 5:00 pm
doobya (7/19/2010)
I wrote a tool for this type of dependency refresh problemit calculates the dependency graph for a group of changing objects
and generates the correct dependency-aware script to refresh the objects
Care to post it?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 20, 2010 at 2:23 am
I plan to one day soon
Even though I use it everyday, it is still a work in progress - and not ready for general consumption
(It is a Windows Application - not a T-SQL script)
July 20, 2010 at 4:52 am
To my knowledge, SQL2008 provides a better and more reliable way to check object dependency in SQL Server than in SQL2005 - it is new sys.sql_expression_dependencies. Some more details in:
http://sqlserverplanet.com/sql-server-2008/find-dependent-objects/
I have not tested the above.
There was never 100% reliable way to find all dependent objects such as stored procs or udfs because of delayed name resolution. Different methods were employed to do so and they would potentially return different results (http://www.mssqltips.com/tip.asp?tip=1294). Also, this task was often achieved by parsing syscomments, however it would find depenedency even if the object name was mentioned in the comment section...
The simple practice to not use SELECT * in views and recreating all UDFs on release can help you to ensure required integrity...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply