Where does SQL Server store the stored procedure code?

Question

I once needed the lines of the stored procedures to be able to trace whether I have a reference to some function, procedure or table, or sometimes to try to find something inside of the sp's code.

Where does SQL Server store the procedure's code?

Answer 1

Use sys.sql_modules because definition is nvarchar(max) because it will not truncate long code.

In INFORMATION_SCHEMA.ROUTINES the ROUTINE_DEFINITION column is only nvarchar(4000) so if you try view the text of a long procedure and you will see that it is truncated.

Use this to search for text in any procedure, view, function:

SELECT DISTINCT
    o.name AS Object_Name,o.type_desc
    FROM sys.sql_modules        m 
        INNER JOIN sys.objects  o ON m.object_id=o.object_id
    WHERE m.definition Like '%'+@Search+'%'
    ORDER BY o.type_desc,o.name 

use this to view the text of a given procedure, view, function:

select * from sys.sql_modules where object_id=object_id('YourProcedure')