Get a full list of procedures from a database

Question

4.00/5 (1 vote)

See more:

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

C#

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));
                }
            }

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

Posted 31-May-12 23:29pm

D3m0n1CMoNkEy

Updated 1-Jun-12 4:57am

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

prateekfgiet · Accepted Answer · 2012-05-31T23:42:00

Solution 1

hi,

try this

SQL

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

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

Posted 31-May-12 23:42pm

prateekfgiet

v2

Comments

D3m0n1CMoNkEy 1-Jun-12 10:58am

I have updated my question, i used the piece of code you gave and i still get an error, please have a look at the updated question to see if you can find out what im doing wrong. Thanks

D3m0n1CMoNkEy 1-Jun-12 12:01pm

thanks with yours and Manfred R. Bihy advice, it works, thank you

Prasad_Kulkarni · Accepted Answer · 2012-05-31T23:45:00

Solution 2

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

SQL

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.

SQL

SELECT *
FROM sys.procedures;

This will ONLY work with SQL Server 2005.

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

Posted 31-May-12 23:45pm

Prasad_Kulkarni

Comments

deepureddy18 1-Jun-12 8:10am

exec sp_stored_procedures

Manas Bhardwaj 1-Jun-12 11:12am

on the spot. +5

Prasad_Kulkarni 2-Jun-12 3:28am

Thank you Manas!

Manfred Rudolf Bihy · Accepted Answer · 2012-06-01T05:08:00

Solution 3

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

Posted 1-Jun-12 5:08am

Manfred Rudolf Bihy

Updated 1-Jun-12 5:09am

v2

Comments

Manas Bhardwaj 1-Jun-12 11:12am

+5

Manfred Rudolf Bihy 1-Jun-12 11:32am

Thanks! :)

D3m0n1CMoNkEy 1-Jun-12 12:00pm

thanks that did the trick ;)

Manfred Rudolf Bihy 1-Jun-12 12:04pm

You're welcome!