SQL Compare is detecting dependent views but not user defined functions.

• 
  susanna,
  
  It's possible that it is either of those - what version are you currently using?
  
  I've just tried to reproduce this in the latest 14.0.6 and I'm actually seeing the opposite behavior where the objects are not seeing the references for objects that are using it.  In trying to replicate your case I created a table with a computed column using a function and then a view on that table.   I deploy the table and I see the function but not the view listed as dependencies.  If I deploy the view I see both the function and table and if I just deploy the function I don't see any dependency.
  
  Kind regards,
  Alex

  Alex B October 18, 2019 15:51

  - 0 +
• 
  Hi @susanna,
  
  Just to add to this, SQL Compare will only include those objects in the dependencies list which are required.  So if you have a table that uses a function, but the function hasn't changed, then it wouldn't need to redeploy the function.  With Views, they may also not need to be included depending on how they are written and what changes were made to the table.
  
  Kind regards,
  Alex

  Alex B October 21, 2019 10:45

  - 0 +
• 
  Hi @Alex B ,
  
  Thank you so much for your answer and sorry for my late response. I have tried to reproduce the issue with the following quite simple example.
  
  I have created a test table and a view and a function depending on it  like follows:
  

  CREATE TABLE test_table

  (

  column1 NVARCHAR(10) NULL,

  colum2 NVARCHAR(10) NULL

  )

  GO

  
  

  CREATE VIEW test_view

  AS

  SELECT *

  FROM test_table

  GO

  
  

  CREATE FUNCTION fn_test_function()

  RETURNS TABLE

  AS

  RETURN

      SELECT *

  FROM test_table

  GO
  
  Then I have dropped column2 from test_table. When deploying  the last change using SQL Compare, in dependencies tab I can only see the view but not the function. So the view dependent on the table is being refreshed but the function not and is not valid anymore. 
  
  The version I am currently using is 14.0.0.  When I am checking for updates I am getting a message that I am using the most up-to-date version.
  
  Many thanks!
  Susanna

  susanna October 25, 2019 13:28

  - 0 +
• 
  Hi @susanna,
  
  Righto, I see this as well.  At first I thought it was because the function didn't list the columns, but the view doesn't either and even listing out the columns didn't cause it to show up as a dependency.
  
  I've escalated this to the development team for further information.
  
  Kind regards,
  Alex

  Alex B November 01, 2019 17:48

  - 0 +
• 
  Hi @Alex B,
  
  Thanks a lot!
  
  Kind regards,
  Susanna
  
  

  susanna November 04, 2019 09:02

  - 0 +
• 
  Hi @susanna,
  
  Righto, so the problem is that we currently only do sp_refreshview and not sp_refreshmodule which is why the view shows up and the function doesn't.
  
  Adding sp_refreshmodule is in the backlog of dependencies work that currently exists, but we don't have a time frame for if or when the items therein will be worked on or added.  I have added a comment to SC-10701 that represents this issue.
  
  Just to add that if the columns are listed out in the view then it wouldn't be included since the sp_refreshview is only run to refresh the expansion of the *.
  
  Also, adding WITH SCHEMABINDING would cause the function and view to need to be altered (at the very least to remove WITH SCHEMABINDING) when the table referenced is changed, which would then cause all of the objects to show up as items to deploy.
  
  I hope that helps clarify what is going on.  I have put a reference to your post here against that work item in the backlog.
  
  I will update here when I get any further information!
  
  Kind regards,
  Alex

  Alex B November 07, 2019 15:46

  - 0 +

Add comment

Please sign in to leave a comment.