Geek Answers Handbook

View SQL Server Stored Procedures Full Relationships

I am working on many very complex stored procedures in SQLSERVER 2012 Express using SQLSEVER 2012 Management studio.

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

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

I tried the view dependency tools provided by SQL Server Management Studio, but sometimes it skips descendant stored procedures when I click View Dependencies of the selected stored procedure. Nor can it show a complete picture of the relationship between these stored procedures.

Is there any tool that can help me? Or any suggestions on how to understand complex stored relationship procedures?

+5
source share
2 answers

I found the following query very 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
+1
source

You can build a recursive query around sys.objects connected in sys.sql_modules (the definition field contains sproc code) and create your own dependency tree.

+1
source

More articles:


All Articles