SQL query all stored procedures (Stored Procedure)

SQL query all stored procedures (Stored Procedure)

 

 

Information_schema.routines View

Requirements: Microsoft SQL Server 2000 or later

You can use the information_schema.routines view to retrieve information about stored procedures. This view contains one row for each stored procedure accessible to the current user in the current database.

For example, the following query returns owner, name and definition text of stored procedures in the current database:

 

Select routine_schema, routine_name, routine_definition
From information_schema.routines
Where routine_type = 'processed'

 

The information_schema.routines view was introduced in SQL Server 2000. This view is based on the sysobjects, syscomments and other system tables.

To retrieve the same information about stored procedures from a Microsoft SQL Server 7.0 database, you can use the following query:

 

Select Su. Name, so. Name, SC. Text
From sysobjects so, syscomments SC, sysusers Su
Where xtype = 'p' and so. ID = SC. ID and so. uid = Su. uid
Order by SU. Name, so. Name, SC. colid

 

The query above may return several rows per each stored procedure if the definition text is longer than 4000 characters.