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

susanna

I have modified a table and  there are some views and functions dependent on that table. After comparison SQL Compare shows in the dependent Object list only the views but not the functions.  On the other hand when I look at the dependencies of the table from SSMS object explorer I can see all the dependencies. Because of this problem the deployment fails and I had to manually drop the function and got it manually deployed. Could this be some setting or permission issue or is this a bug?

Alex B

@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

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

susanna

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

Alex B

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

susanna

Hi @Alex B,

Thanks a lot!

Kind regards,
Susanna

Alex B

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