Find List stored procedures in SQL Server database

 /********************************************************************************************/

/*

   Example 1: List stored procedures in SQL Server database

*/

/********************************************************************************************/

SELECT COUNT(*) FROM sys.procedures

/********************************************************************************************/

/*

   Example 2: List stored procedures in SQL Server database

*/

/********************************************************************************************/

SELECT COUNT(*) SPCOUNT 

  FROM INFORMATION_SCHEMA.ROUTINES

 WHERE ROUTINE_TYPE='PROCEDURE'


/********************************************************************************************/

/*

   Example 3: List stored procedures in SQL Server database

*/

/********************************************************************************************/

 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;


/**************************************************************************************/

ANIL SINGH

Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..

My Tech Blog - https://www.code-sample.com/
My Books - Book 1 and Book 2

www.code-sample.com/. Powered by Blogger.
^