![]() ![]() ![]() |
Ispirer SQLWays Database Migration Software
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='PROCEDURE'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.colidThe query above may return several rows per each stored procedure if the definition text is longer than 4000 characters.
![]() ![]() ![]() |