Solved

Retrieve a list of User Stored Procedures in SQL server

Posted on 2004-04-16
16
2,546 Views
Last Modified: 2007-12-19
Hi,

I try to create a simple Window, which list down all the available User Store Procedures on the Server, and get the info like the Parameters available, the Stored Procedure code, etc.

My idea will be enter the Parameters in the form then return the Result into a DataGrid, etc.

Any idea? thks
0
Comment
Question by:Ryan Chong
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
16 Comments
 
LVL 28

Assisted Solution

by:mmarinov
mmarinov earned 83 total points
ID: 10840886
you can use
select * from sysobjects where sysobjects.xtype='U'
for xtype:

Object type. Can be one of these object types:
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure


B..M
0
 
LVL 52

Author Comment

by:Ryan Chong
ID: 10841042
Hi mmarinov,

I was able to get the list of User Stored Procedures by using :

select * from sysobjects
where sysobjects.xtype='P' and sysobjects.category = '0'
order by name

Is this statement correct?

However, the above only retrieve the name of the User Stored Procedure. Do we haven any methods available to retrieve the Stored Procedures Info?

thks
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 10843665
when I get to the office I will post some code I wrote that uses the sp_helptext (which returns the SQL of the sproc) it then parses this to get info on the sproc.
0
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 
LVL 52

Author Comment

by:Ryan Chong
ID: 10848728
Still waiting for any suggestion..
0
 
LVL 52

Author Comment

by:Ryan Chong
ID: 10852418
Hi,

I was able to retrieve the info of Stored Procedures i want by using similar query below:

SELECT     TOP 100 PERCENT dbo.sysobjects.id, dbo.sysobjects.xtype, dbo.sysobjects.name, dbo.sysobjects.uid, dbo.sysobjects.category,
                      dbo.syscolumns.name AS ColName, dbo.systypes.name AS ColType, dbo.syscomments.text AS Command
FROM         dbo.sysobjects INNER JOIN
                      dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id INNER JOIN
                      dbo.syscomments ON dbo.sysobjects.id = dbo.syscomments.id INNER JOIN
                      dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype
WHERE     (dbo.sysobjects.category = 0) AND (dbo.sysobjects.xtype = 'P')
ORDER BY dbo.sysobjects.name, dbo.syscolumns.name

What i'm still asking is, is that any other methods can be used for retrieve Database Objects Info from ADO, ADOX or even ADO.NET ?
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 10853321
See the OpenSchema method of the ADO Connection object.
0
 
LVL 52

Author Comment

by:Ryan Chong
ID: 10853476
Hi emoreau,

Do you have any existing samples or links? thks
0
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 240 total points
ID: 10853488
0
 
LVL 52

Author Comment

by:Ryan Chong
ID: 10853568
Hi emoreau,

As i follow the link, i found this link:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;309488

>>Note that no method is equivalent to GetOleDbSchemaTable when you use a SqlClient.SqlConnection object. The SQL Server .NET Data Provider exposes backend schema information through stored procedures and informational views. For more information about views and stored procedures that are available through Microsoft SQL Server, see the Transact-SQL reference in the MSDN Library.

And i'm doing an Application using SqlClient.SqlConnection object, so is that means is it currently Not available??

or do you have a version of "OpenSchema" using SqlClient.SqlConnection in ADO.Net? I just curious if i can do that too..

For your link above, i will try the code shortly, thks
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 10853581
Why don't you want to use SQL views?
0
 
LVL 52

Author Comment

by:Ryan Chong
ID: 10853584
For your information, i'm trying to explore the vb.net component too..
0
 
LVL 52

Author Comment

by:Ryan Chong
ID: 10853604
Can SQL Views accept parameters, No, right?? or I'm wrong?

I try to create some procedures on the SQL Server, and then try to load it to a Form, list down the parameters available, and other information by using the Select statement above. Then after entered the parameters values, the result is shown in a DataGrid. For your info, i have done that successfully.

Just now a bit on learning to see if i can do that in similar way ;-) thks
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 10853621
I was talking about the SQL views that are returning databases objects.
0
 
LVL 52

Author Comment

by:Ryan Chong
ID: 10853765
ok, i try using the OpenSchema already, it retrieve the database objects successfully by using:

Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set conn = New ADODB.Connection
    conn.ConnectionString = "Driver={SQL Server};Server=SERVER;Database=x;Uid=xx;Pwd=xxx;"
    conn.Open
    Set rs = conn.OpenSchema(adSchemaTables)
   
    For i = 1 To rs.RecordCount
        Debug.Print rs("TABLE_NAME")
        rs.MoveNext
    Next i
   
    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing

I can see the System Objects, with the Views there.. but can't see any Stored Procedures there..

Maybe the final question, is that possible to retrieve the Stored Procedures using OpenShema, or got any other solution?

thks
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 240 total points
ID: 10853953
In an application I did long time ago, I found that the best way to retrieves SPs and their paramaters was to use a mix of sysObjects and OpenSchema:

            strSQL = "SELECT Name " & _
                     "FROM SysObjects " & _
                     "WHERE xtype = 'P' " & _
                     "AND Category = 0 " & _
                     "ORDER BY Name"
            Set rstInfo = New ADODB.Recordset
            rstInfo.Open strSQL, pconConnection, adOpenStatic, adLockReadOnly
            Do Until rstInfo.EOF
                strSPName = rstInfo.Fields("Name").Value
                Set rstParam = pconConnection.OpenSchema(adSchemaProcedureParameters, Array(Empty, Empty, strSPName, Empty))
                Do Until rstParam.EOF
                    strParamName = rstParam.Fields("PARAMETER_NAME").Value
                    strParamName = Replace(strParamName, "@", "")
...
0
 
LVL 52

Author Comment

by:Ryan Chong
ID: 10854045
Thks emoreau, it works, i also able to try out like: Set rs = conn.OpenSchema(adSchemaProcedures) to get the Stored Procedures as well.

Thks, i learn something here ;-) cheers
0

Featured Post

Stressed Out?

Watch some penguins on the livecam!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question