Is it possible to get the definition of a Stored Procedure using the name (without having to look in the object explorer)

Question

I'm working on a database that has an huge number of stored procedures. The frustrating part is that the naming convention is not consistent. I spent half of my time looking for them in the object explorer.

I'd like to know whether there's a way to get the definition of a SPROC just by knowing the name? Something like:

SELECT commande(name_of_SPROC) --> Display the definition of the SPROC

Just anything that can help me get the definition of a SPROC without having to search in the Object explorer of the SQL Server Management Studio.

Thanks for helping

Answer 1

Yes, there are many ways:

1. Using system SP: sp_helptext:

sp_helptext 'your_sp_name'

2. Using INFORMATION_SCHEMA views:

select ROUTINE_DEFINITION, * 
from INFORMATION_SCHEMA.ROUTINES 
where ROUTINE_NAME = 'your_sp_name';

3. Using sys.sql_modules view:

select definition, *
from sys.sql_modules 
where object_name(object_id) = 'your_sp_name'

4. Using sys.syscomments view:

select text, * 
from sys.syscomments 
where object_name(id) = 'your_sp_name'

The best one to use is the sp_helptext, its also very handy and fast. Other option I like is sql_modules sys view. Do not use syscomments sys view as it splits bigger SPs into multiple rows, check here.