Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009

    Unanswered: Transact-SQL procedure references


    I need to know all references (tables, columns) called by an SQL procedure.
    Ideally I would like to run a batch with an sql procedure file as input.
    The output will be the list of tables and columns which are referenced in this proc.
    Do you now a tool or an API which can do that?

    I tested VisualExpert but this software can not give me this output in batch mode (as it is called, it is visual ).


  2. #2
    Join Date
    Jun 2007
    Have a look at the procedure sp_depends. If you can't use this directly then try looking at the sql code for this routine.

  3. #3
    Join Date
    Jan 2009
    Tks for your reply but sp_depends only provides the table dependencies list. I need also the column which are referenced in the sql proc.
    Moreover I need those dependencies between databases:
    MyDB1..MyProc depends on MyDB2..MyTable1.Col1


  4. #4
    Join Date
    Mar 2009
    Sydney, Australia
    I do not understand why you expect to get this information from SQL or Sybase, they do not suggest that they provide CASE tool features. Other vendors provide full-fledged CASE or documentary tools which sit on top of Sybase (or Oracle or MS). Most of them are visual only; some provide strong reporting (what you call batch?) capability. Even the Db Modelling tools provide good reporting along these lines.

    This is easily done, but you need to understand the limits of the processes and procs you are using. Obviously you are not talking about the input/output columns to the proc (those are in syscolumns). sp_depends is a good start, yes it shows tables used. Now exactly which "columns that are referenced in the proc" are you talking about ? Have you run sp_depends on each of the tables identified in the first sp_depends, and that is not it ?

    If you want only the columns touched/referenced within proc(s), and not all columns in the referenced table, you need a documentation or CASE tool. Which is why we document procs and transactions before the fact.

    In Sybase, there is no such thing as dependencies such as "MyDB1..MyProc depends on MyDB2..MyTable1.Col1"; there is such a thing as "MyDB1..MyProc depends on MyDB2..MyTable1" and ""MyDB1..MyTableA depends on MyDB2..MyTableB"".

    If you wish, and particularly for your batch need, you can write your own proc to extract the info. Look at sysreferences, syscolumns and the code for sp_depends.
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

Posting Permissions

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