Post reply

list tables and columns referenced by functions, stored procs, views

  • December 9, 2009 at 7:13 pm

    #137657

    Hi All

    I'm looking for some TSQL that will look through all the user defined functions, stored procs, and views (for a given database) and list all the tables and the columns within those tables that are referenced by the aforementioned objects.

    Cheers

    GOC

  • December 9, 2009 at 7:51 pm

    #1090320

    sp_depends yourtable

    select * from sysdepends where object_id(id) = 'YourTable'

    this will accurately identify any dependencies help by views or functions and is accurate for most procedures; however, because of a feature of creating procedures called late binding, it is possible to create a procedure that refere3nces a table that does not exist. if the procedure was created BEFORE the table it uses is created, no entry would exist in the dependencies.

    also, anything that uses dynamic SQL would not have any dependencies, even though it might really use a given table.

    not very many people create procs before the tables they reference, so for many it is a non-issue, but something you should keep in mind.

    because it is not possible to create an FK, function or view that references something that does not exist, you don't have to worry about inaccurate results for those types of items.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 10, 2009 at 8:31 am

    #1090604

    IN 2005 you can also use sys.sql_dependencies which can even take you down to the column level.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply