/********************************************************************************************/
/*
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 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..
|