locked
CodeMap like for Stored Procedures RRS feed

  • Question

  • I am looking into ways to develop CodeMap like feature for t-SQL Stored Procedures/Functions.  Is there an OOTB tool available for something like this or a reasonably easy way to develop this?  Any insights is greatly appreciated.


    Gaja Kannan

    Tuesday, March 5, 2013 6:04 PM

Answers

  • Try:

    SELECT ReferencingObjectType = o1.type,
    
           ReferencingObject = SCHEMA_NAME(o1.schema_id)+'.'+o1.name,
    
           ReferencedObject = SCHEMA_NAME(o2.schema_id)+'.'+ed.referenced_entity_name,
    
           ReferencedObjectType = o2.type 
    
    FROM   AdventureWorks2008.sys.sql_expression_dependencies ed 
    
           INNER JOIN  AdventureWorks2008.sys.objects o1 
    
             ON ed.referencing_id = o1.object_id 
    
           INNER JOIN AdventureWorks2008.sys.objects o2
    
             ON ed.referenced_id = o2.object_id
    
    WHERE o1.type in ('P','TR','V', 'TF')
    
    ORDER BY ReferencingObjectType, ReferencingObject
    
    /*
    ReferencingObjectType	ReferencingObject	ReferencedObject	ReferencedObjectType
    ....
    TF	dbo.ufnGetContactInformation	Person.BusinessEntityContact	U 
    TF	dbo.ufnGetContactInformation	Person.ContactType	U 
    TF	dbo.ufnGetContactInformation	Sales.Customer	U 
    TF	dbo.ufnGetContactInformation	HumanResources.Employee	U 
    TF	dbo.ufnGetContactInformation	Person.Person	U 
    TF	dbo.ufnGetContactInformation	Sales.Store	U 
    .....
    */


    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Windows Azure SQL Database Programming & Design

    • Proposed as answer by Naomi N Monday, March 11, 2013 12:00 AM
    • Marked as answer by Naomi N Monday, March 11, 2013 12:00 AM
    Wednesday, March 6, 2013 3:30 PM
  • I finally found the solution to find dependencies in SQL...

    SELECT 
    	ao.Name
    	,CallerType = SUBSTRING(v1.name, 5,66)
    	,'ReferencededObject' = (s3.name + '.' + o2.name)
    	,ReferencedType = SUBSTRING(v2.name, 5, 66)
    	,'ReferencedColumn' = COL_NAME(s1.depid, s1.depnumber)
    FROM
    	sys.all_objects ao
    	INNER JOIN
    		sys.objects o1
    	ON
    		ao.object_id = o1.object_id
    	INNER JOIN
    		master.dbo.spt_values v1
    	ON
    		o1.type = SUBSTRING(v1.name,1,2) COLLATE DATABASE_DEFAULT
    	INNER JOIN
    		sysdepends s1
    	ON
    		o1.object_id = s1.id
    	INNER JOIN
    		sys.objects o2
    	ON
    		o2.object_id = s1.depid
    	INNER JOIN
    		master.dbo.spt_values v2
    	ON
    		o2.type = SUBSTRING(v2.name,1,2) COLLATE DATABASE_DEFAULT AND v2.type = 'O9T'
    	INNER JOIN
    		sys.schemas s3
    	ON
    		o2.schema_id = s3.schema_id
    WHERE
    	ao.type IN ('P','FN')
    	AND
    	ao.is_ms_shipped = 0
    	AND
    	(ao.name like 'udf_%'
    	 OR
    	 ao.name like 'usp_%')
    ORDER BY
    	ao.Name
    It is a breakdown of sysdepends system procedure. Now I can write a C# code to create template dgml or T4 template to generate a visual dependency view.
     

    Gaja Kannan

    • Marked as answer by GajaKannan Friday, March 15, 2013 6:20 PM
    Friday, March 15, 2013 6:19 PM
  • SELECT 
        SUBSTRING(v1.name, 5,66)
        , UPPER((s4.name + '_' + ao.name))
        , ISNULL(UPPER((s3.name + '_' + o2.name)), 'NULL')
        , ISNULL(SUBSTRING(v2.name, 5, 66), 'NULL')
        , ISNULL(COL_NAME(s1.depid, s1.depnumber), 'NULL')    
    FROM  
    	sys.all_objects ao
    	LEFT OUTER JOIN 
    		sysdepends s1
    		ON
    			ao.object_id = s1.id
    	INNER JOIN
            master.dbo.spt_values v1
            ON
            ao.type = SUBSTRING(v1.name,1,2) COLLATE DATABASE_DEFAULT
    	INNER JOIN
    		sys.schemas s4
    	ON
    		ao.schema_id = s4.schema_id        
        LEFT OUTER  JOIN
            sys.objects o2
        ON
            o2.object_id = s1.depid
        LEFT OUTER  JOIN
            master.dbo.spt_values v2
        ON
            o2.type = SUBSTRING(v2.name,1,2) COLLATE DATABASE_DEFAULT AND v2.type = 'O9T'
        LEFT OUTER  JOIN
            sys.schemas s3
        ON
            o2.schema_id = s3.schema_id                
    WHERE 
        ao.type IN ('P','FN')
        AND
        ao.is_ms_shipped = 0
        AND
        (ao.name like 'udf_%'
         OR
         ao.name like 'usp_%')
    ORDER BY
    	SUBSTRING(v1.name, 5,66), ao.name, o2.name

    Improved version of the above SQL.  The previous iteration of the SQL missed objects that does not have any dependency


    Gaja Kannan

    • Marked as answer by GajaKannan Wednesday, March 20, 2013 4:37 PM
    Wednesday, March 20, 2013 4:37 PM

All replies