List stored procedures in SQL Server database

Query below return all stored procedures and information about it in SQL Server database.

Query

select schema_name(obj.schema_id) as schema_name,
       obj.name as procedure_name,
       case type
            when 'P' then 'SQL Stored Procedure'
            when 'X' then 'Extended stored procedure'
        end as type,
        substring(par.parameters, 0, len(par.parameters)) as parameters,
        mod.definition
from sys.objects obj
join sys.sql_modules mod
     on mod.object_id = obj.object_id
cross apply (select p.name + ' ' + TYPE_NAME(p.user_type_id) + ', ' 
             from sys.parameters p
             where p.object_id = obj.object_id 
                   and p.parameter_id != 0 
             for xml path ('') ) par (parameters)
where obj.type in ('P', 'X')
order by schema_name,
         procedure_name;

Columns

  • schema_name - name of the database (schema)
  • procedure_name - name of the function/procedure
  • type - type of procedure
    • SQL Stored Procedure
    • Extended stored procedure
  • parameters - name of parameters with their data type separated by comma ','
  • definition - text of the SQL statement executed by the function/procedure

Rows

  • One row - represents one procedure
  • Scope of rows: - all procedures in database
  • Ordered by - schema name and procedure name

Sample results

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
0
There are no comments. Click here to write the first comment.