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.colid 

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

             

Copyright © 1999-2015 Ispirer Systems Ltd. Ispirer and SQLWays are trademarks of Ispirer Systems Ltd. All other product names may be trademarks of the respective companies. All rights reserved.