Analysing 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 analyser 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 MS 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
About this entry
You’re currently reading “Analysing dependencies in MSSQL,” an entry on worksonmymachine
- Published:
- 2011/03/31 / 19:16
- Category:
- Uncategorized
- Tags:
No comments yet
Jump to comment form | comment rss [?] | trackback uri [?]