[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Retrieve a list of User Stored Procedures in SQL server

Posted on 2004-04-16
16
Medium Priority
?
2,565 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 332 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 53

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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 53

Author Comment

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

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 53

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 960 total points
ID: 10853488
0
 
LVL 53

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 53

Author Comment

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

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 53

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 960 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 53

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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.   …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

650 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