Are stored procedure query texts stored in any tables?
There are several ways to get this that require different input information

INFORMATION_SCHEMA:

You only need to know the name of the object you want the definition for.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'MyProcName'


Note, this schema is database specific, so you need to be in the context of the database where the procedure exists for this query to return the correct data. Also note, it only returns the first 4000 characters of the procedure.

OBJECT_DEFINITION:

You need to know the object_id of the procedure you're looking for.

SELECT OBJECT_DEFINITION(*object_id*)


Database-specific as well, but returns the complete text of the object.

sys.sql_modules:

You need to know the object_id of the procedure you're looking for.

SELECT * FROM sys.sql_modules WHERE object_id = *object_id*


Database-specific as well, but returns the complete text of the object.