sql查询查所有存储过程(stored procedure)

最新推荐文章于 2025-10-13 18:02:29 发布

原创最新推荐文章于 2025-10-13 18:02:29 发布 · 7.6k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#存储 #sql #sql server #microsoft #database #query

.Net 专栏收录该内容

75 篇文章

订阅专栏

本文介绍如何使用SQL查询当前数据库中的所有存储过程。针对不同版本的SQL Server提供了两种查询方法：一种适用于SQL Server 2000及以后版本，另一种则适用于SQL Server 7.0。这些方法利用了INFORMATION_SCHEMA.ROUTINES视图或系统表来检索存储过程的所有者、名称和定义。

sql查询查所有存储过程(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='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.