本文介绍如何使用 INFORMATION_SCHEMA.ROUTINES 视图来检索 SQL Server 中存储过程的相关信息，包括所有者、名称及定义等。适用于 SQL Server 2000 及以上版本。
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'
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
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
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
