Below are three simple ways to use a SQL query to get the text of a stored procedure, trigger, function, etc.

Method #1: SYS.SQL_MODULES

sys.sql_modules is a system DMV (Dynamic Management View) that returns a row for each object that is an SQL language-defined module in SQL Server. Objects of type P, RF, V, TR, FN, IF, TF, and R have an associated SQL module.

Here is an example:

-----------------------------------
select a.[definition] from sys.sql_modules as [a]
 join sys.objects as [b] on b.[object_id] = a.[object_id]
where b.name = 'YourStoredProcedureName';
-----------------------------------

Method #2: sp_helptext

sp_helptext is a built-in stored procedure that displays the definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, user-defined Transact-SQL function, trigger, computed column, CHECK constraint, view, or system object such as a system stored procedure.

Here is an example:

-----------------------------------
exec sp_helptext @objname = 'YourStoredProcedureName';
-----------------------------------

Method #3: sys.syscomments

sys.syscomments contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the original SQL definition statements.

Here is an example:

-----------------------------------
select a.[text] from sys.syscomments as [a]
 join sys.sysobjects as [b] on b.id = a.id
where b.name = 'YourStoredProcedureName';
-----------------------------------