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.

  • CONCAT_WS() for SQL Server
  • How to integrate open ID Login
  • Database does not exist error
  • How to join and pivot, link and group a table
  • Upgrade SQL Server 2000 to 2008 R2 with replication
  • Log table Schema changes
  • My question: Is there any way to access to the whole stored procedure definition when it is longer than 4.000 characters?

  • How do you auto-commit an SQL Server transaction in JPA?
  • Store aggregate data in a table or a view?
  • SQL Server: should I use information_schema tables over sys tables?
  • I can't attach a database using sql server 2008
  • How do I preview a destructive SQL query?
  • How to get a date in YYYY-MM-DD format from a TSQL datetime field?
  • 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.