List stored procedures in Azure SQL database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-04-01

Table of Contents:


    Query below return all stored procedures and information about it in Azure SQL 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

    0
    There are no comments. Click here to write the first comment.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept