Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Track flow of SP's

Answered Track flow of SP's

  • Wednesday, March 20, 2013 2:43 PM
     
     
    Sign In to Vote
    0

    Hi 

    Is there a way to track what all the store procedure are doing which tables they affect or maybe just  which SP is connected to how many tables?

    I have 2 system with nearly 600+ SP's and 400+ tables i think it will be easy to track.

    I don't to see what are they doing (It can be good though :)), just the connections.

    Thank you!

     

All Replies

  • Wednesday, March 20, 2013 2:47 PM
     
     
    Sign In to Vote
    0

    SQL Dependency Tracker from Red Gate may be an alternative:
    http://www.red-gate.com/products/sql-development/sql-dependency-tracker/

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Wednesday, March 20, 2013 2:58 PM
     
      Has Code
    Sign In to Vote
    0

    Hi,

    Try this..

    SELECT DISTINCT so.name,  sod.name
FROM 
sysobjects so
LEFT JOIN sysdepends sd ON sd.id = so.id
LEFT JOIN sysobjects sod ON sod.id = sd.depid
WHERE so.xtype = 'P'

    you can also use object_name(id) or object_name(depid) to derive the object name pertaining to that id, but for some objects it returns null.

    So joining with the sysobjects will fetch the correct object name..

    Regards,

    Brindha.

     
  • Wednesday, March 20, 2013 4:44 PM
     
     
    Sign In to Vote
    0

    Hi Thank you guys!

    But i think i have fail to explain my self.

    I want to see Store Procedure > which tables it affects

    update product name SP > product table

                                     > sale table (maybe)

                                    > Warehouse tables (maybe)

    please let me know if i should provide more info.

    Thank you!

     
  • Wednesday, March 20, 2013 11:05 PM
     
     Answered
    Sign In to Vote
    0

    Yes, I think I understood that.

    The problem is that this is not as simple as it may seem, because SQL Server does not track dependencies very well and anything reliable needs to be based on analysing the source code. Admittedly, I don't know if the tool I pointed you does this, but I would still recommed that you give it a try. (There is a 14-day evaluation.)

    SQL Server provides to catalog views that tracks dependencies: sys.sql_dependencies and sys.sql_expression_dependencies. The latter is available from SQL 2005.

    The problem with the first view is that if you drop a table and create it, you lose all dependency information for it. The latter has a problem with queries that involves temp tables. (So has the first, but there is a workaround, albeit a very difficult one unless you have a tool, which I'm lucky too have.)

    The one you should place your bets on is probably sys.sql_expression_dependencies, but I'm not giving you any queries, because I have nothing canned - I never use it myself. There are also two DMVs, sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities.

    There is also SQL Server Data Tools, which is a free download and which supports all versions from SQL 2005 and on. I don't know who well it performes dependency analysis.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked As Answer by DesipmDB Thursday, March 21, 2013 6:24 PM
    •  
     
  • Thursday, March 21, 2013 6:25 PM
     
     
    Sign In to Vote
    0
    Thank you for your help i'll try your mention tool :) and see if it can help..
     
  • Thursday, March 21, 2013 9:01 PM
     
      Has Code
    Sign In to Vote
    0
    Hi, Couple of days back I came across a similar question on this forum which had title like "CodeMap for Database Objects". They were using the below SQL Query. This would be helpful if you do not want to use a third party tool. Hope this helps. 
    SELECT 
    SUBSTRING(v1.name, 5,66) AS StoredProcedure
    , UPPER((s4.name + '_' + ao.name)) AS NameOfStoredProcedure
    , ISNULL(SUBSTRING(v2.name, 5, 66), 'NULL') AS [DatabaseObject]
    , ISNULL(UPPER((s3.name + '_' + o2.name)), 'NULL') AS [NameOfTable]    
    , ISNULL(COL_NAME(s1.depid, s1.depnumber), 'NULL') AS [Column]   
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

     
  • Thursday, March 21, 2013 10:44 PM
     
     
    Sign In to Vote
    0

    Please note that this query presumes that your sysdepends (which is built on top of sys.sql_dependencies) is accurate. This is not very likely.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se