CODEWITHSUNDEEP
sqlsql-serverssms
leeblee00
leeblee00

Reputation: 1

How do I get a table of a database's stored procedures (excluding system stored procedures) along with its definition?

This is what I have now. This gets me a list of the stored procedures, but I need an additional column with their respective definition.

SELECT 
name 
FROM sys.objects
WHERE type = 'P'
order by name

I've also tried adding another line below the SELECT (definition as OBJECT_DEFINITION).

Upvotes: 0

Views: 203

Answers (1)

Raymond Holmboe
Raymond Holmboe

Reputation: 2171

Here is how to get the definition and stored procedure name in Sql Server

SELECT o.name, m.definition
FROM sys.objects o
INNER JOIN sys.sql_modules m 
ON o.object_id = m.object_id
WHERE o.type = 'P';

Upvotes: 2

Related Questions