Produce a Dependency Table

The first step to creating a diagram is to generate a dependency table for the stored procedure you want to see. This is similar to what you get when you run 'View Dependencies' on an object in the SSMS object explorer, a set of relationships between the object of choice and other objects it references or is referenced by.

This is implemented as a user defined function GraphDoc.udf_cs_DepTable. It takes two parameters for the schema and procedure names, and returns a table in the format of the user-defined table type GraphDoc.udtt_DependencyGraph. The snippet below shows the beginning code of this function.

CREATE FUNCTION [GraphDoc].[udf_cs_DepTable]
(
	@BaseObjectSchema nvarchar(255), -- Disambiguate in case of namesakes
	@BaseObjectName nvarchar(255)	-- A single stored procedure name
)
/* 
	The return table here is defined like the user-defined table type udtt_DependencyGraph.
	As yet a udf can't return a udtt so we're stuck with doing it this way.
	Assumption is that object requested will be in the same database as the calling routine.
	An object might feasibly have namesakes in the database in different schemas which is why the 
	schema name is required.
*/
RETURNS @returntable TABLE
(
	  ServerName nvarchar(255)
	, DBName nvarchar(255)
	, BaseObjectName nvarchar(255)
	, BaseObjectType nvarchar(255)
	, ParentObjectSchemaName nvarchar(255)
	, ParentObjectName nvarchar(255)
	, ParentObjectType nvarchar(255)
	, ThisObjectSchemaName nvarchar(255)
	, ThisObjectName nvarchar(255)
	, ThisObjectType nvarchar(255)
	, Level smallint
	, referenced_database_name nvarchar(255)
	, referenced_server_name nvarchar(255)
	, srcdest tinyint
	, loc int
)

so for example, if you have installed GraphDoc to AdventureWorks2017 and run this in SSMS

Select distinct * from [GraphDoc].[udf_cs_DepTable] ('dbo', 'uspGetBillOfMaterials')

you will get

It's a pretty simple structure so hopefully much of that is self-explanatory. The srcdest value indicates the CRUD usage of the table, so 1=Read, 2=Insert, 4=Update, 8=Delete, or any sum of those values. The loc value is the number of lines of code in procedure. It's used for info on the final diagram so the developer knows what they're getting into.