List stored procedures in SQL Server database

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


select schema_name(obj.schema_id) as schema_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,
from sys.objects obj
join sys.sql_modules mod
     on mod.object_id = obj.object_id
cross apply (select + ' ' + 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,


  • 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


  • 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.
There are no comments. Click here to write the first comment.