Solved

Retrieve a list of User Stored Procedures in SQL server

Posted on 2004-04-16
16
2,531 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 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

20 Experts available now in Live!

Get 1:1 Help Now