SQL Server Performance

Stored procedures

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by darkangelBDF, Nov 7, 2008.

  1. darkangelBDF Member

    Good day. I hope everyone's doing well. I have another annoying question.
    A colleague of mine wants to know if there's a script / way that he could use to establish which stored procedures access certain tables. Is there any way this can be found out?
  2. madhuottapalam New Member

    You may use system sp like sp_depends or sp_msdependencies or you may refer this blog post
    thanks
    Madhu
  3. FrankKalis Moderator

    A quick and dirty way could be to query sys.sql_modules for the table name in question.
  4. mufford New Member

    If you're looking for all stored procedures that reference the object, then maybe this will help. It does the same thing as sp_depends, but can be filtered to a specific column.
    Declare @tableName varchar(128)
    , @columnName varchar(128);

    Select @tableName = 'myTableName'
    , @columnName = Null; --'myColumnName', optional

    Select o.name As 'dependency'
    , Max(Cast(d.is_selected As tinyint)) As 'is_selected'
    , Max(Cast(d.is_updated As tinyint)) As 'is_updated'
    From sys.columns As c With (NoLock)
    Join sys.tables As t With (NoLock)
    On c.object_id = t.object_id
    Join sys.sql_dependencies d With (NoLock)
    On t.object_id = d.referenced_major_id
    And d.class = 0
    And d.referenced_minor_id = c.column_id
    Join sys.objects o With (NoLock)
    On d.object_id = o.object_id
    Where t.[name] = @tableName
    And c.[name] = IsNull(@columnName, c.name)
    Group By o.name
    Order By o.name;

Share This Page