Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: all stored procs using a particular table (or view)?

    is there a way? how? thanks!
    Last edited by g11DB; 07-20-10 at 22:36.

  2. #2
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    A bit difficult - stored procedures can be used to construct dynamic SQL that the engine cannot definitively log.

    You could in theory do something with the text column in sys.syscomments - but that would involve some manual searching to see where your table name crops up. If the table name is unique and distinct enough you can get fairly accurate results with a straight where text like '%your table name here%' - but if there are similarly named tables/variables etc used you will pick them up too and need to filter them out.

    Eg:
    Code:
      SELECT	DISTINCT 
    		Object_Name(ID) AS Object_Name
      FROM		Sys.Syscomments SS
      WHERE		Text LIKE '%Table_Name_Here%'
    If you want to see what touches your ORDERS table and you have an ORDERS_HISTORY, or have say NUMORDERS as a column in other tables, then you'll have a pretty difficult time.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd give this a shot to see if it works for you:
    Code:
    SELECT s.[name], o.[name], d.[referenced_server_name]
    ,  d.[referenced_database_name], d.[referenced_schema_name]
    ,  d.[referenced_entity_name]
       FROM sys.objects AS o
       JOIN sys.schemas AS s
          ON (s.[schema_id] = o.[schema_id])
       JOIN sys.sql_expression_dependencies AS d
          ON (d.[referencing_id] = o.[object_id])
       WHERE d.[referenced_entity_name] IS NOT NULL
       ORDER BY s.[name], o.[name], d.[referenced_server_name]
    ,  d.[referenced_database_name], d.[referenced_schema_name]
    ,  d.[referenced_entity_name]
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jul 2006
    Posts
    111
    hi guys, thanks for the reply.

    hi pat. your sql, which database do i run it against? i ran it against master but it doesn't know the sys.sql_expression_dependencies object.

    many thanks in advance!

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What version of SQL are you running? I don't remember if sys.sql_expression_dependencies joined the party in 2005 or in 2008, but if you are running something earlier it won't be there.

    I could actually cook up a similar view against sysdepends, but that would require you to recompile all of the views, procedures, triggers, and functions in order for it to be completely accurate.

    -PatP
    Last edited by Pat Phelan; 07-21-10 at 09:15. Reason: Fixed typographical error
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    which version of sql are you running. that system view is new in either 2k5 or 2k8.

    edit.sniped.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •