How to find the name of stored procedure, based on table name search, using SQL Server 2008?

Question

I want to find all of the stored procedures where a particular table is being used. There are lots of stored procedures in the database, so it's not feasible to check each procedure.

Is there any way to use a search query so that I can find the stored procedures?

I have tried this code:

SELECT distinct so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '% RejectionReason %'

Where RejectionReason is my table name, but it shows all procedures where RejectionReason is used as column name, so that doesn't work.

Answer 1

I am using the following SQL script to search for column names and text inside all stored procedures of your database. You can use it as well to find tables in stored procedures.

Specify the search term in variable @SearchFor(as you would use it in a LIKE expression, e.g. '%LastName%' to find columns and stored procedures containing LastName).

It will find column names in tables as well as text inside stored procedures. The script can even display the SP source code, if you set @SPNameOnlyto 0.

--
-- Purpose: Search field names in all tables, views stored procedures
--

DECLARE @SearchFor nvarchar(max)='%Search_SP_Or_Table_Or_View%' -- search for this string
DECLARE @SearchSP bit = 1 -- 1=search in SPs as well
DECLARE @DisplaySPSource bit = 1 -- 1=display SP source code

-- tables
if (@SearchSP=1) begin  
  (
  select '['+c.table_Schema+'].['+c.table_Name+'].['+c.column_name+']' [schema_object], 
            t.table_type 
  from information_schema.columns c
  left join information_schema.Tables t on c.table_name=t.table_name
  where column_name like @SearchFor or t.table_name like @SearchFor 
  UNION
  select '['+routine_Schema+'].['+routine_Name+']' [schema_object], 
         'PROCEDURE' as table_type from information_schema.routines
  where routine_definition like @SearchFor or routine_name like @SearchFor 
        and routine_type='procedure'
  )
  order by table_type, schema_object
end else begin
  select '['+c.table_Schema+'].['+c.table_Name+'].['+c.column_name+']' [schema_object], 
         t.table_type 
  from information_schema.columns c
  left join information_schema.Tables t on c.table_name=t.table_name
  where column_name like @SearchFor or t.table_name like @SearchFor 
  order by c.table_Name, c.column_name
end     
-- stored procedure (source listing)
if (@SearchSP=1) begin      
    if (@DisplaySPSource=1) begin
      select '['+routine_Schema+'].['+routine_Name+']' [schema.sp], routine_definition 
      from information_schema.routines
      where routine_definition like @SearchFor  or routine_name like @SearchFor 
      and routine_type='procedure'
      order by routine_name
    end
end

Answer 2

Here is a piece of code hope it will work. Just changes the table name it depends upon your code

SELECT DISTINCT so.name 
FROM syscomments sc INNER JOIN sysobjects so on sc.id=so.id 
WHERE sc.text LIKE '%tablename%'

e.g.:

SELECT DISTINCT so.name 
FROM syscomments sc INNER JOIN sysobjects so on sc.id=so.id 
WHERE sc.text LIKE '%users%'

You will get the list of store procedures and the table relations.

Answer 3

SysObjects stores basic information about all objects inside your database. It's useful for you to know because it tells us the name of each object and the type of the object.

SysComments stores the actual text (code) for your stored procedures and functions. It contains an ID field that maps back to the id field in SysObjects.

select so.name, text
from sysobjects so, syscomments sc
where so.id = sc.id
and text like '%RejectionReason%'

Answer 4

This will return SP's and Views.

SELECT DISTINCT o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m 
INNER JOIN sys.objects o 
    ON m.object_id=o.object_id
WHERE m.definition Like '%TableName%'

Answer 5

SELECT o.name, o.type_desc, p.name, p.type_desc
FROM sys.sql_dependencies d
INNER JOIN sys.objects o
    ON d.object_id = o.object_id
INNER JOIN sys.objects p
    ON d.referenced_major_id = p.object_id
    AND o.name = 'RejectionReason'

or

SELECT o.name, t.TABLE_NAME, c.text 
  FROM syscomments c 
  JOIN sysobjects o 
    ON c.id = o.id
  JOIN INFORMATION_SCHEMA.Tables t
    ON  c.text LIKE '%RejectionReason%' 

or

EXEC sp_depends @objname = N'RejectionReason';

if none of those help you check this blog: http://blog.sqlauthority.com/2010/02/04/sql-server-get-the-list-of-object-dependencies-sp_depends-and-information_schema-routines-and-sys-dm_sql_referencing_entities/

Answer 6

As per MSDN sp_depends will be removed in future releases in case you are using that, you can use the following query instead:

SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('dbo.TableName', 'OBJECT');

Answer 7

I guess this script shows all the dependent object of the table, including SPs.

USE MYDatabase
GO

DECLARE @TableName varchar(100)
SET @TableName = 'mytable'

SELECT
 SourceSchema                  = OBJECT_SCHEMA_NAME(sed.referencing_id)
 ,SourceObject                 = OBJECT_NAME(sed.referencing_id)
 ,ReferencedDB                 = ISNULL(sre.referenced_database_name, DB_NAME())
 ,ReferencedSchema             = ISNULL(sre.referenced_schema_name,
OBJECT_SCHEMA_NAME(sed.referencing_id))
 ,ReferencedObject             = sre.referenced_entity_name
 ,ReferencedColumnID   = sre.referenced_minor_id
 ,ReferencedColumn             = sre.referenced_minor_name
FROM sys.sql_expression_dependencies sed
CROSS APPLY sys.dm_sql_referenced_entities(OBJECT_SCHEMA_NAME(sed.referencing_id)
+ '.' + OBJECT_NAME(sed.referencing_id), 'OBJECT') sre
WHERE sed.referenced_entity_name = @TableName
AND sre.referenced_entity_name = @TableName

for more details you can check out. http://sqlserverplanet.com/sql-server-2008/find-dependent-objects/

Answer 8

There are two possibilities I am aware of.

Firstly SQL Management Studio has an option to show Dependencies. Right-click on the Table and select View Dependencies However, this will not highlight usps where the tablename is embedded in dynamic SQL.

The second option is to right click on the database and select Generate Scripts. Follow the wizard and script all the usps to a new query window, then search that for the name of your table. This is more laborious, but will find all uses.

Answer 9

Try to use RedGate's free tool SQL Search.