Solved

Retrieve a list of User Stored Procedures in SQL server

Posted on 2004-04-16
16
2,534 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
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 49

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
 
LVL 49

Author Comment

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

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 69

Expert Comment

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

Author Comment

by:Ryan Chong
ID: 10853476
Hi emoreau,

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

Assisted Solution

by:Éric Moreau
Éric Moreau earned 240 total points
ID: 10853488
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 49

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 69

Expert Comment

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

Author Comment

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

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 69

Expert Comment

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

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 69

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 49

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

IP addresses can be stored in a database in any of several ways.  These ways may vary based on the volume of the data.  I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.   …
The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows applica…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now