SQL Server Performance

t-sql 2005

Discussion in 'SQL Server 2005 General Developer Questions' started by jassie, Dec 28, 2010.

  1. jassie New Member

    For 'selected' stored procedure(s) in 'selected' databases, I would like to know the t-sql statement(s) to find all the tables and views used used.
    Better yet for all the 'stored procedures' in a database, I would like to t-sql statments to see what tables and views the stored procedures are using.
    jassie, Dec 28, 2010
    #2

  2. FrankKalis Moderator

    I'm not aware of any out-of-the-box T-SQL solution to your question and this might turn out a really tedious task. There are third-party tools available to could list such dependencies, but they are not free afaik. You could see the definition of a stored procedure by joining sys.objects with sys.sql_modules and then have a look at the various FROM clauses, but that not be as accurate as you want it. Also, if a procedure, for example, uses a UDF lookup function to resolve stuff by looking at some table, do you consider this table as being used by the procedure or not?
    FrankKalis, Dec 28, 2010
    #3

  3. satya Moderator

    To some extent you can get information from INFORMATION_SCHEMA views which are system generated, so read on about thsoe IS views in Books ONline.
    satya, Dec 29, 2010
    #4

  4. Luis Martin Moderator

    There is a lot of 3rd party tools for database documentation to do that.
    You can download for try from Red Gate that tool.
    Luis Martin, Dec 29, 2010
    #5

  5. satya Moderator

    Luis, your suggestion will help if the OP is looking to document the database and database objects. I don't think that is the case here....anyways good to see the reference.
    satya, Jan 4, 2011
    #6

  6. FrankKalis Moderator

    Satya,
    I think Luis is referring to tools like Red Gate's SQL Dependency Tracker that to a certain extend give the information required.
    FrankKalis, Jan 4, 2011
    #7

  7. Luis Martin Moderator

    [quote user="FrankKalis"]
    Satya,
    I think Luis is referring to tools like Red Gate's SQL Dependency Tracker that to a certain extend give the information required.
    [/quote]
    Yeap![:)]
    Luis Martin, Jan 4, 2011
    #8

  8. satya Moderator

    See that clears up lot of confused-air .. now [:)], thats what I mean better to point the name.
    satya, Jan 5, 2011
    #9

  9. Madhivanan Moderator

    Starts with
    EXEC sp_depends 'procedure name'
    Madhivanan, Jan 28, 2011
    #10

  10. yuanyelss New Member

    You could see the definition of a stored procedure by joining sys.objects with sys.sql_modules and then have a look at the various FROM clauses, but that not be as accurate as you want it.
    yuanyelss, Feb 18, 2011
    #12

  11. ismailadar New Member

    Hi,
    You can use a cursor in ordeer to find which sp depends on which table or view:declare @SPInfoTable table ( Id
    int identity(1,1), name
    varchar(100) NOT null,type varchar(100),updated varchar(20),selected varchar(10),coloumnName varchar(100) ) declare
    @sql varchar(1024)declare curs cursor for
    select
    top 10 'sp_depends ' + ''''+OBJECT_NAME(object_id)+'''' from sys.sql_modulesopen
    cursfetch
    next from curs into @sqlwhile
    (@@fetch_status = 0)begin
    insert
    into @SPInfoTable exec
    (@sql)fetch
    next from curs into @sqlend
    close
    cursdeallocate
    cursselect
    * from @SPInfoTable
    ismailadar, Feb 20, 2011
    #13

  12. satya Moderator

    I recommend INFORMATION_SCHEMA views than going to system catalogs directly.
    satya, Mar 7, 2011
    #14

Share This Page