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.

Some blog posts I've written about this that may be useful: sqlblog.org/blogs/aaron_bertrand/archive/2009/11/09/… sqlblog.org/blogs/aaron_bertrand/archive/2008/09/09/… — Aaron Bertrand
– Aaron Bertrand, Commented Aug 3, 2011 at 13:46

Carlos Quintanilla · Accepted Answer · 2013-12-31 15:21:01Z

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/

-1 for being incorrect. Instead of @objectname, it must be @objname. Will convert it to +1 when corrected.

Alex_L · Accepted Answer · 2011-08-03 09:27:11Z

4

Try to use RedGate's free tool SQL Search.

answered Aug 3, 2011 at 9:27

Alex_L

2,6641 gold badge15 silver badges13 bronze badges

Comments

shA.t · Accepted Answer · 2017-01-18 10:38:54Z

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.

Works like a charm, i tested in SQL Server 2014 as well! Thank you

S2S2 · Accepted Answer · 2012-08-03 11:15:21Z

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');

BonyT · Accepted Answer · 2011-08-03 09:27:52Z

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.

Sumit · Accepted Answer · 2011-08-03 09:35:39Z

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/

Nalan Madheswaran · Accepted Answer · 2015-09-18 05:47:38Z

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%'

Karthi Rathinavelu · Accepted Answer · 2016-07-06 05:32:38Z

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%'

Matt · Accepted Answer · 2018-02-05 13:59:27Z

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

Collectives™ on Stack Overflow

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

9 Answers 9

1 Comment

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

9 Answers 9

1 Comment

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Linked

Related