Object dependencies in SSMS not reliable?

Question

I needed to make an change to the definition of a table in a SQL Azure database.

This is a change which may cause problems to objects which reference this table. In SSMS, I have right-clicked on the table --> View dependencies to see which objects I need to check to see if they're going to be affected by my change.

However, in going through the list, I've found a couple of views which, based on their names, I figured may have a dependency on my table. I was correct - the dependency existed. However, these views were not listed in the Object Dependencies dialog box in SSMS.

This is making me concerned that there are other objects which have a dependency which I may have missed.

Why might this have happened? Can I do anything to make the dependency check more reliable?

Answer 1

Can I do anything to make the dependency check more reliable?

Call sp_refreshview or create the views with SCHEMABINDING.

Here's an old, old trick every SQL Server DBA knows. Run a query like this in SSMS

select concat('exec sp_refreshview ''', quotename(schema_name(schema_id)),'.', quotename(name),''';')
from sys.views

But use "Results to Text" CTRL-T or

Then copy the script from the results pane and run it.

You could also use a cursor and dynamic SQL, or in newer versions of SQL Server you can generate the whole batch and run it like this:

declare @SQL nvarchar(max) = 
(select STRING_AGG( concat('exec sp_refreshview ''', quotename(schema_name(schema_id)),'.', quotename(name),''';'), CHAR(13)+CHAR(10) )
from sys.views)

exec sp_executesql @SQL