References to Views/Stored Procedures

Question

Hi, How can I find out what other views/stored procedures are using a specific view/stored procedure? Thanks Lennie

Answer 1

sp_depends will:

Displays information about database object dependencies, such as the views and procedures that depend on a table or view, and the tables and views that are depended on by the view or procedure.

Note:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities instead.

As @marc mentioned, Redgate's SQL Dependency Tracker is worth the investment.

Answer 2

There are excellent tools out there that help you with this - e.g. Red-Gate's SQL Dependency Tracker. Well worth the investment - finding dependencies is a challenge you'll face over and over again.

Answer 3

The quickest SQL way with no 3rd party tools is to search the view/proc/udf definition

SELECT OBJECT_NAME(object_id) FROM sys.sql_modules WHERE definition LIKE '%WhatToLookFor%"

For SQL Server 2000 (as per your tag), use syscomments

The internal dependency tracking of SQL Server is notoriously, er, shit, at least before sys.sql_expression_dependencies (related to Mitch's answer). And definitely in SQL Server 2000.