how to find store procedure that contain both of two tables?
i have two tables, one is transaction table that contains day to day transactions and second table contains history for first table, there is one sp in the database that transfers records from transaction table to history table and vice versa. how to find that perticular sp from the database? i know the names of both hte tables but i dont know the name of store procedure and there are lot of SPs in the database.
This should be what you need: SELECT OBJECT_NAME(id) , text FROM syscomments WHERE [text] LIKE '%tablenamehere%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1 GROUP BY OBJECT_NAME(id), text --- [Edit] Following Matt's comment here is a script that will return what you need even if the procedures are huge and split over different data pages but it only works on SS2005+ SELECT * FROM [sys].[sql_modules] AS sm INNER JOIN [sys].[objects] AS o ON [sm].[object_id] = [o].[object_id] WHERE [o].[type] = 'p' AND ( [sm].[definition] LIKE '%%table2%' OR [sm].[definition] LIKE '%%%' )
You could try the sysdepends system view: SELECT id, depid, SPName = OBJECT_NAME(id), TableName = OBJECT_NAME(depid) FROM sysdepends For example: DECLARE @SPName VARCHAR(128) = '' DECLARE @T1Name VARCHAR(128) = '' DECLARE @T2Name VARCHAR(128) = '' SELECT id, depid, SPName = OBJECT_NAME(id), TableName = OBJECT_NAME(depid) FROM sysdepends WHERE id = (SELECT id FROM sysobjects WHERE NAME= @SPName) AND depid = (SELECT id FROM sysobjects WHERE NAME = @T1Name) AND id in (SELECT id FROM sysdepends WHERE depid = (SELECT id FROM sysobjects WHERE NAME = @T2Name)) OK, so it's not pretty, but it seems to work.
I will add a little apart from @Fatherjack suggestions - you can use EXEC sp_depends @objname - SELECT routine_name, routine_type FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%TableName%' and routine_type = 'PROCEDURE' - exec dbo.sp_MSdependencies [