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.
  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?
  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.
  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.
  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.
  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.
  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![:)]
  8. satya Moderator

    See that clears up lot of confused-air .. now [:)], thats what I mean better to point the name.
  9. Madhivanan Moderator

    Starts with
    EXEC sp_depends 'procedure name'
  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.
  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
  12. satya Moderator

    I recommend INFORMATION_SCHEMA views than going to system catalogs directly.

Share This Page