Searching a string in stored procedure definitions fails when the definition is too long
I run this query to find references to
MyTable in the stored procedures in my data base:
SELECT ROUTINE_NAME, ROUTINE_DEFINITION, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%MyTable%'
However, I did not find anything, the problem is that ROUTINE_DEFINITION is limited to 4000 characters and the SP that operated on
MyTable was longer. Using
View Dependencies on the table did not work.
My question: Is there any way to access to the whole stored procedure definition when it is longer than 4.000 characters?
One Solution collect form web for “Searching a string in stored procedure definitions fails when the definition is too long”
Can you try with
SELECT DISTINCT O.[NAME], C.[text] FROM sysobjects O JOIN syscomments C ON C.id = O.id WHERE C.[text] LIKE '%MyTable%'
UPDATE: As suggested by marc_s, you can use sys.procedures and sys.sql_modules to get the definitions:
SELECT DISTINCT p.[name], m.[definition] FROM sys.procedures p JOIN sys.sql_modules m ON m.[object_id] = p.[object_id] WHERE m.[definition] LIKE '%MyTable%'