从数据库获取过程的完整列表 [英] Get a full list of procedures from a database

问题描述

如何从特定数据库获取完整的过程列表?

我已经获得了数据库名称，现在我所要做的就是使用该数据库名称来获取该特定数据库的过程的完整列表.

请帮忙...

数据库名称将被放入字符串变量``strDbName''.

这是我到目前为止所拥有的...

how do you get a full list of procedures from a specific database?

I have gotten the database name, and now all i need to do is use that db name to get a full list of procedures for that specific database.

please help...

the db name will be put into a string variable ''strDbName''.

This is what i have so far...

string dbName = listBox1.SelectedItem.ToString();
            string conxString_2 = "Data Source=LUKE-PC; Integrated Security=True; Initial Catalog=" + dbName + "";

            using (SqlConnection sqlConx_2 = new SqlConnection(conxString_2))
            {
                sqlConx_2.Open();

                SqlCommand sqlcom = new SqlCommand();
                sqlcom.Connection = sqlConx_2;
                sqlcom.CommandType = CommandType.StoredProcedure;
                sqlcom.CommandText = "select * from sys.objects where type='p' and is_ms_shipped=0 and [name] not like 'sp[_]%diagram%'";

                SqlDataReader sqldr;
                sqldr = sqlcom.ExecuteReader();

                while (sqldr.Read())
                {
                    listBox1.Items.Add(sqldr.GetString(0));
                }
            }

它在(sqldr = sqlcom.ExecuteReader();)处显示错误，表明找不到存储过程''''.

我只是不知道我在做什么错，请帮助...

It shows an error at (sqldr = sqlcom.ExecuteReader();) saying, Could not find stored procedure ''''.

I just dont know what i''m doing wrong please help...

推荐答案

试试这个

try this

Select * from sys.objects where type=’p’ and is_ms_shipped=0 and [name] not like ‘sp[_]%diagram%’

我是从
那里拿来的 listing-dropping-all-stored-procedures

i have taken this from
listing-dropping-all-stored-procedures

使用sp_stored_procedures的最简单方法是不带参数调用它:

The simplest way to use sp_stored_procedures is to call it with no arguments:

exec sp_stored_procedures

参考:列出存储过程 [

Ref: List stored procedures[^]

Or else

Run following simple script on SQL Server 2005 to retrieve all stored procedure in database.

SELECT *
FROM sys.procedures;

这仅适用于SQL Server2005.

也可以看看:
列出MySQL中的存储过程 [列出数据库中的所有存储过程 [

This will ONLY work with SQL Server 2005.

Also have look on:
List stored procedures in MySQL[^]
List All Stored Procedure in Database[^]

而不是使用 CommandType.Text [ Prasad Kulkarni's [

Instead of using CommandType.StoredProcedure[^] you need to use CommandType.Text[^]. The reason is that you are not calling a stored procedure, but rather calling an SQL statement that queries the DMBS''s system tables.

If you were to use Prasad Kulkarni''s[^] advice of calling a system stored procedure to enumerate all stored procedures, then CommandType.StoredProcudure would be nescessary.

Regards,

Manfred

这篇关于从数据库获取过程的完整列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！