Retrieve a list of User Stored Procedures in SQL server

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
LVL 56
Ryan ChongAsked:
Who is Participating?
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
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
 
mmarinovConnect With a Mentor Commented:
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
 
Ryan ChongAuthor Commented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
gregoryyoungCommented:
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
 
Ryan ChongAuthor Commented:
Still waiting for any suggestion..
0
 
Ryan ChongAuthor Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
See the OpenSchema method of the ADO Connection object.
0
 
Ryan ChongAuthor Commented:
Hi emoreau,

Do you have any existing samples or links? thks
0
 
Ryan ChongAuthor Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
Why don't you want to use SQL views?
0
 
Ryan ChongAuthor Commented:
For your information, i'm trying to explore the vb.net component too..
0
 
Ryan ChongAuthor Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
I was talking about the SQL views that are returning databases objects.
0
 
Ryan ChongAuthor Commented:
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
 
Ryan ChongAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.