SQL Server view Stored Procedures full relationships

Question

I am working on a bunch of very complicated stored procedures on SQLSERVER 2012 Express databases using SQLSEVER 2012 Management studio.

For example I have a stored procedure SP1 calling another stored procedure SP2 and SP2 calls SP3, SP3 calls SP4 ...

What I would like to see is a diagram that can provide me the full relationships between these stored procedures and all the tables that being used in these stored procedures.

I have tried the view dependencies tools provided by SQL Server management studio, but sometimes it misses descendants stored procedures when I click "View Dependencies" of a selected stored procedure. Also it can't show a full picture of relationships between these stored procedures.

Is there any tool can help me with this? Or any suggestions on how to understand complicated relationship stored procedures?

Answer 1

I found following query quite useful, hope it can help.

select      
        distinct 
        ObjJectType=
        case Obj.xType
        When 'V' then
            'VIEW'
        When 'P' then
            'PROC'
        When 'D' then
            'INDEX'
        When 'TF' then
            'TABLE FUNCTION'
        When 'FN' then
            'FUNCTION'
        When 'U' then
            'TABLE'
        When 'F' then
            'FK CONSTRAINT'
        When 'PK' then
            'PK CONSTRAINT'
        When 'TR' then
            'TRIGGER'
        When 'S' then
            'SYSTEM OBJECT'
        end ,
        Obj.name
from 
        syscomments Com inner join sysobjects Obj
  on  Com.id = Obj.id
where 
        Com.text like '%sp_name%' 
order by Obj.name

Answer 2

You can build a recursive query around sys.objects joined on sys.sql_modules (the definition field contains the sproc code) and build yourself a dependencies tree.