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.

  • SQL Column after insert read only without trigger
  • Specify default Rails database
  • T-SQL, assign values to multiple variable from the same column in one SELECT
  • Picking Random Names
  • How can I get the ID of the last INSERTed row using PDO with SQL Server?
  • Efficient paging with large tables in sql 2008
  • My question: Is there any way to access to the whole stored procedure definition when it is longer than 4.000 characters?

  • Using CASE Statement inside IN Clause
  • SQL Server INFORMATION_SCHEMA contents
  • SQL Table Counting and Joining
  • How to stuff duplicate records in SQL server?
  • Column “invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause”
  • Pagination with Microsoft sql & PHP
  • One Solution collect form web for “Searching a string in stored procedure definitions fails when the definition is too long”

    Can you try with sysobjects and syscomments

    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%'
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.