Analyzing Dependencies in MSSQL

I’m working on a project where almost all the logic is in database objects and there are a few developers working on it. Now, in this context, it is extremely difficult to keep track of the dependencies. One of the things that Red Gate Dependency analyzer is good for is showing you the tree of dependencies in a DB, and of course it looks like we’ll eventually buy the thing and be done with it. However, in its absence, I was tasked with coming up with an interim solution. sp_dependencies doesn’t always work because the Microsoft implementation is buggy, and so I decided to go the long way round.

The following script analyses the text of each individual object and builds a tree of what depends on what. It sure as hell is the long way round, and come Monday week I don’t think I’ll need this, but I wrote it and by Gum I’m gonna keep it somewhere. I used the results of this to build idempotent setup/teardown deployment scripts for my database deployments. On a DB of 40 gigs and thousands of objects, it takes around 90 seconds to run. Enjoy!

SET NOCOUNT ON;

DECLARE
@ObjectName			NVARCHAR(MAX),
@ObjectType			NVARCHAR(MAX),
@SchemaName			NVARCHAR(MAX),
@SchemaId			NVARCHAR(MAX),
@ObjectFullName			NVARCHAR(MAX),
@ObjectId			INT,
@SortOrder			INT,
@CommandCursor			CURSOR,
@object_definition		NVARCHAR(MAX),
@block				NVARCHAR(MAX),
@printblocksize			INT,
@lengthleft			INT,
@lengthtoprint			INT,
@crlf				NVARCHAR,
@constraint_full_name		NVARCHAR(MAX),
@constraint_name		NVARCHAR(MAX),
@table_full_name		NVARCHAR(MAX),
@constraint_definition		NVARCHAR(MAX)
;
DECLARE @Objects TABLE(
ObjectId   INT,
SchemaId   INT,
SchemaName NVARCHAR(MAX),
ObjectType NVARCHAR(40),
ObjectName NVARCHAR(MAX),
ObjectCode NVARCHAR(MAX)
);
DECLARE @Dependencies TABLE (
DependentId INT,
DependsOnId INT
);
DECLARE @DependencyAnalysis TABLE (
DependentId INT,
DependsOnId INT,
Depth       INT
);

-- grab all objects and their text
INSERT INTO @Objects
SELECT
object_id,
schema_id,
SCHEMA_NAME(schema_id) AS schemaname,
CASE Type
WHEN 'AF'      THEN 'AGG_FUNC_CLR'		-- Aggregate function (CLR)
WHEN 'C'       THEN 'CHECK_CONSTRAINT'	-- CHECK constraint
WHEN 'D'       THEN 'DEFAULT'		-- DEFAULT (constraint or stand-alone)
WHEN 'F'       THEN 'FOREIGN_KEY'		-- FOREIGN KEY constraint
WHEN 'FN'      THEN 'SCALAR_FUNCTION_SQL'	-- SQL scalar function
WHEN 'FS'      THEN 'SCALAR_FUNCTION_CLR'	-- Assembly (CLR) scalar-function
WHEN 'FT'      THEN 'TABLE_FUNCTION_CLR'	-- Assembly (CLR) table-valued function
WHEN 'IF'      THEN 'TABLE_FUNCTION_INLINE'	-- SQL inlinetable-valued function
WHEN 'IT'      THEN 'INTERNAL_TABLE'		-- Internal table
WHEN 'P'       THEN 'STORED_PROC'		-- SQL Stored Procedure
WHEN 'PC'      THEN 'STORED_PROC_CLR'	-- Assembly (CLR) stored-procedure
WHEN 'PG'      THEN 'PLAN_GUIDE'		-- Plan guide
WHEN 'PK'      THEN 'PRIMARY_KEY'		-- PRIMARY KEY constraint
WHEN 'R'       THEN 'RULE'			-- Rule (old-style, stand-alone)
WHEN 'RF'      THEN 'REPLICATION_FILTER_PROCEDURE'	-- Replication-filter-procedure
WHEN 'S'       THEN 'SYSTEM_BASE_TABLE'               	-- System base table
WHEN 'SN'      THEN 'SYNONYM'                         	-- Synonym
WHEN 'SQ'      THEN 'SERVICE_QUEUE'                   	-- Service queue
WHEN 'TA'      THEN 'DML_TRIGGER_CLR'                 	-- Assembly (CLR) DML trigger
WHEN 'TF'      THEN 'TABLE_FUNCTION_SQL'              	-- SQL table-valued-function
WHEN 'TR'      THEN 'DML_TRIGGER_SQL'                 	-- SQL DML trigger
WHEN 'TT'      THEN 'TABLE_TYPE'                      	-- Table type
WHEN 'U'       THEN 'TABLE_USER_DEFINED'              	-- Table (user-defined)
WHEN 'UQ'      THEN 'UNIQUE_CONSTRAINT'               	-- UNIQUE constraint
WHEN 'V'       THEN 'VIEW'                            	-- View
WHEN 'X'       THEN 'EXTPROC'                         	-- Extended stored procedure
ELSE 'UNKNOWN'
END,
Name,
Text
FROM	sys.objects WITH (NOLOCK),
syscomments WITH (NOLOCK)
WHERE  ( sys.objects.object_id = syscomments.id )
ORDER  BY sys.objects.name ASC;

-- analyse the text of all the objects to determine who needs who
INSERT INTO @Dependencies SELECT a.ObjectId AS DependentId,
b.ObjectId  AS DependsOnId
FROM   @Objects a,
@Objects b
WHERE  ( a.ObjectName <> b.ObjectName )
AND Charindex(Upper(b.ObjectName), Upper(a.ObjectCode)) <> 0;

;WITH analysis_cte
AS (SELECT DependentId AS DependentId,
DependsOnId AS DependsOnId,
1             AS Depth
FROM   @Dependencies a
WHERE  NOT EXISTS (SELECT *
FROM   @Dependencies b
WHERE  b.DependsOnId = a.DependentId)
UNION ALL
SELECT c .DependentId AS DependentId,
c.DependsOnId  AS DependsOnId,
d.Depth + 1    AS Depth
FROM   @Dependencies c
INNER JOIN analysis_cte d
ON c.DependentId = d.DependsOnId
WHERE d.Depth < 5
)
INSERT INTO @DependencyAnalysis
SELECT *
FROM   analysis_cte
GROUP  BY	DependentId,
DependsOnId,
Depth
ORDER  BY	Depth;

SELECT
do.ObjectId
,	do.SchemaName
,	do.ObjectName
,	do.ObjectCode
,	de.SchemaName
,	de.ObjectName
,	da.Depth
FROM
@DependencyAnalysis da
INNER JOIN @Objects do
ON do.ObjectId = da.DependentId
INNER JOIN @Objects de
ON de.ObjectId = da.DependsOnId