CODEWITHSUNDEEP
sql-serverstored-proceduressql-server-2012
VJNDAR KALYAN
VJNDAR KALYAN

Reputation: 11

How to find the stored procedures acting on a table in SQL Server 2012?

I have a table in a database. The fields of the table are being updating by some stored procedure from some other table. Is there any possibility that I can find the stored procedures which are acting on a particular table???

Upvotes: 1

Views: 330

Answers (3)

Alan
Alan

Reputation: 334

Check out the sys.dm_sql_referencing_entities view.

But this won't work if the SP build dynamic SQL to access the table (not schema bound). You might need to use trace or SQL Profiler to help out.

Upvotes: 1

zedfoxus
zedfoxus

Reputation: 37059

If you want to know which stored procedures are likely to touch your table, you could search that in sys.procedures something on the lines of:

select * from sys.procedures 
where object_definition(object_id) like '%tablename%'

-- or probe information_schema
select * from information_schema.routines
where routine_definition like '%tablename%'

Object_Definition

Returns the Transact-SQL source text of the definition of a specified object. https://msdn.microsoft.com/en-us/library/ms176090(v=sql.105).aspx

When we look into sys.procedures, there'll be a field called object_id. Each object in SQL Server has an ID. object_definition will retrieve definition of that object - in our case, text of the procedure will be retrieved.

Upvotes: 2

devil1212
devil1212

Reputation: 13

Maybe you use this query. You got a list store procedure in actual database

 SELECT ROUTINE_NAME   FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_TYPE= 'PROCEDURE'

Upvotes: -1

Related Questions