asked
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 '%%%' )
Given that the Dharmendrab is looking for a SP with two tables, it shouldn't be difficult for him to expand that query... ...WHERE [text] LIKE '%table1name%' AND [text] LIKE '%table_name%'... Or was that supposed to be left as an exercise for the OP? ;-)
was hoping that wasn't too large a leap of faith to expect ... You have got me thinking though. will be back in a bit
Ok, just wanted to test how these compare: SELECT OBJECT_NAME(id), text FROM syscomments WHERE ([text] LIKE '%table1%' AND [text] LIKE '%table2%') AND OBJECTPROPERTY(id, 'IsProcedure') = 1 GROUP BY OBJECT_NAME(id), text go SELECT OBJECT_NAME(id), text FROM syscomments WHERE ([text] LIKE '%table1%table2%' or [text] LIKE '%table2%table1') AND OBJECTPROPERTY(id, 'IsProcedure') = 1 GROUP BY OBJECT_NAME(id), text both result in same plan!!
Almost - but this won't find things whereby the text of the proc is bigger than 4K, and each object reference sits in it's own 4K chunk, or where one or both of the object references sit on the boundary between 4K chunks. On 2005 and 2008, sys.sql_modules eases your pain...
@Matt - OK, thanks for the info, not had much reason to go into this. Is it same syntax but using sql_modules rather than syscomments??
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 [
