StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This article on TechRepublic </p> <p><a href="https://web.archive.org/web/1/http://blogs.techrepublic%2ecom%2ecom/datacenter/?p=277" rel="nofollow noreferrer">Finding dependencies in SQL Server 2005</a></p> <p>describes a way to do that:</p> <blockquote> <p>This tutorial will show how you can write a procedure that will look up all of the objects that are dependent upon other objects.</p> </blockquote> <p>Here is the code to create the system stored procedure for finding object dependencies:</p> <pre><code>USE master GO CREATE PROCEDURE sp_FindDependencies ( @ObjectName SYSNAME, @ObjectType VARCHAR(5) = NULL ) AS BEGIN DECLARE @ObjectID AS BIGINT SELECT TOP(1) @ObjectID = object_id FROM sys.objects WHERE name = @ObjectName AND type = ISNULL(@ObjectType, type) SET NOCOUNT ON ; WITH DependentObjectCTE (DependentObjectID, DependentObjectName, ReferencedObjectName, ReferencedObjectID) AS ( SELECT DISTINCT sd.object_id, OBJECT_NAME(sd.object_id), ReferencedObject = OBJECT_NAME(sd.referenced_major_id), ReferencedObjectID = sd.referenced_major_id FROM sys.sql_dependencies sd JOIN sys.objects so ON sd.referenced_major_id = so.object_id WHERE sd.referenced_major_id = @ObjectID UNION ALL SELECT sd.object_id, OBJECT_NAME(sd.object_id), OBJECT_NAME(referenced_major_id), object_id FROM sys.sql_dependencies sd JOIN DependentObjectCTE do ON sd.referenced_major_id = do.DependentObjectID WHERE sd.referenced_major_id <> sd.object_id ) SELECT DISTINCT DependentObjectName FROM DependentObjectCTE c END </code></pre> <blockquote> <p>This procedure uses a Common Table Expression (CTE) with recursion to walk down the dependency chain to get to all of the objects that are dependent on the object passed into the procedure. The main source of data comes from the system view sys.sql_dependencies, which contains dependency information for all of your objects in the database.</p> </blockquote>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload