Most of the time, we need to know not only the views that depend on a table, but any other object, as well. For that matter, we often want to know what depends on other types of objects, such as UDFs, stored procedures, and views. For this I have a handy script I'll share with you. We use various schemas in out databases, so this script enables you to use the qualified name of the object.

/*

Find all dependencies on objects

1. Set @SEARCH_TABLE_NAME = NULL to see all dependencies

2. Set @SEARCH_TABLE_NAME to name of object for which you need to know what other objects

depend on it.

3. Result is list of objects that depend on the object dependencies

*/

DECLARE @SEARCH_TABLE_NAME VARCHAR(200) = NULL

DECLARE @TABLE_NAME SYSNAME = PARSENAME(@SEARCH_TABLE_NAME,1)

DECLARE @TABLE_SCHEMA_NAME SYSNAME = PARSENAME(@SEARCH_TABLE_NAME,2)

IF @SEARCH_TABLE_NAME IS NOT NULL -- only display dependent object information

SELECT DISTINCT o.type_desc AS [DependentObjectType], os.name+'.'+OBJECT_NAME(d.object_id) [DependentObjectName]

FROM sys.sql_dependencies d

INNER JOIN sys.objects o

ON d.object_id = o.object_id

INNER JOIN sys.schemas os

ON o.schema_id = os.schema_id

INNER JOIN sys.objects do

ON d.referenced_major_id = do.object_id

INNER JOIN sys.schemas dos

ON do.schema_id = dos.schema_id

WHERE d.object_id != do.object_id

AND OBJECT_NAME(referenced_major_id) = @TABLE_NAME

AND (@TABLE_SCHEMA_NAME IS NULL OR dos.name = @TABLE_SCHEMA_NAME)

ORDER BY 2

ELSE -- Display both dependent object and referenced object information

SELECT DISTINCT o.type_desc AS [DependentObjectType], os.name+'.'+OBJECT_NAME(d.object_id) [DependentObjectName],

do.type_desc AS [ReferencedObjectType],dos.name+'.'+OBJECT_NAME(referenced_major_id) [ReferencedObjectName]

FROM sys.sql_dependencies d

INNER JOIN sys.objects o

ON d.object_id = o.object_id

INNER JOIN sys.schemas os

ON o.schema_id = os.schema_id

INNER JOIN sys.objects do

ON d.referenced_major_id = do.object_id

INNER JOIN sys.schemas dos

ON do.schema_id = dos.schema_id

WHERE d.object_id != do.object_id

AND (@TABLE_NAME IS NULL OR OBJECT_NAME(referenced_major_id) = @TABLE_NAME)

AND (@TABLE_SCHEMA_NAME IS NULL OR dos.name = @TABLE_SCHEMA_NAME)

ORDER BY 2,3