To retrieve all the functions/procedures in the given database -- using Information_schema related view

Most of the SQL Server guys, some times need to retrieve the information about the procedures and functions.

Information about Functions:

----------------------------

The below query gives the no.of functions that are there in specific database and the definition of that specific function.

Query:

-------

Select 

'['+ROUTINE_SCHEMA+'].'+'['+ROUTINE_NAME+']' AS RoutineName,

ROUTINE_TYPE as RoutineType,

ROUTINE_DEFINITION

from 

INFORMATION_SCHEMA.ROUTINES

where 

ROUTINE_TYPE='FUNCTION'

Information about Procedures:

------------------------------

The below query gives the no.of procedures that are there in specific database and the definition of that specific procedure.

Query:

-------

Select 

'['+ROUTINE_SCHEMA+'].'+'['+ROUTINE_NAME+']' AS RoutineName,

ROUTINE_TYPE as RoutineType,

ROUTINE_DEFINITION

from 

INFORMATION_SCHEMA.ROUTINES

where 

ROUTINE_TYPE='PROCEDURE'