Geek asks and answers

SQL Server Management Studio Alternative View Dependencies

I have long known that the "View Dependencies" function in SQL Server Management Studio is borderline useless, since it is unlikely to ever return all the dependencies, however, is there a better alternative? This is a pretty powerful feature for obvious reasons, but if you can't rely on the results (as is the case with Microsoft), you still need to do everything by hand to be safe.

It seems like it would be pretty easy to implement, are there any tools out there that deal with this effectively?

As a note, does anyone know what is wrong with "View Dependencies"? Is there a certain class of dependencies that it faces with the problem of identification?

+5
source share
4 answers

Red Gate has a tool called SQL Dependency Tracker that works very well.

You can also write a request to the sys.sql_dependencies view, which will provide some basic information. Similar to this:

SELECT o.name, o.type_desc, p.name, p.type_desc
FROM sys.sql_dependencies d
INNER JOIN sys.objects o
    ON d.object_id = o.object_id
INNER JOIN sys.objects p
    ON d.referenced_major_id = p.object_id

Here is an article on finding dependencies:

Finding Dependencies in SQL Server 2005

+2
source

Late, but hopefully useful as the recommended tool is free ...

Im uses a similar tool for recommended by David - ApexSQL Search . They claim to have their own dependency tracking mechanism that works for everything except dynamic SQL.

I have not tested it in detail to confirm this, though ...

: , .

+4

. , . , "WidgetUser":

, . :

select 'EXEC sys.sp_refreshsqlmodule ''dbo.' + OBJECT_NAME(m.object_id) + ''''
from sys.sql_modules m
where m.definition like '%WidgetUser%'

Then paste this output text and run the updates. It will be something like this:

EXEC sys.sp_refreshsqlmodule 'dbo.up_WidgetUser_Select'
EXEC sys.sp_refreshsqlmodule 'dbo.up_WidgetUser_Update'
EXEC sys.sp_refreshsqlmodule 'dbo.WidgetUserView'
EXEC sys.sp_refreshsqlmodule 'dbo.ufx_WidgetUser_Fooinize'
...

Now that your dependencies are updated, run sp_depends:

EXEC sp_depends @objname = N'dbo.WidgetUser'
0
source

More articles:


All Articles