Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: VB6
Expertise: Intermediate
Jun 17, 1999

Retrieve the list of SQL Server stored procedure

You can query SQL Server system tables to retrieve the list of all the stored procedure that are available for the end user and applications. The following routine extract their names and loads them in a DataGrid control:

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim connString As String, sql As String

' Open a connection    
connString = "Provider=SQLOLEDB;Data Source=P2;Initial Catalog=Pubs"
cn.Open connString, "sa", ""
' Open the recordset
sql = "Select * from sysobjects where type = 'P' and category = 0"
rs.Open sql, cn, adOpenStatic, adLockReadOnly, adCmdText
' Load the results in a DataGrid control
Set DataGrid1.DataSource = rs
DataGrid1.Refresh
The resulting Recordset contains several pieces of useful information, such as NAME (the stored procedure's name), ID (unique identification number), and CRDATE (its creation date). To extract additional information on its arguments, however, it is necessary to query another system table:

' This code assumes that the RS recordset is corrently
' pointing to the stored procedure for which you want
' to extract its parameters
Dim rs2 As New ADODB.Recordset, sql2 As String
sql2 = "SELECT syscolumns.Name, systypes.Name, syscolumns.length " & _
    "FROM syscolumns, systypes " & "WHERE syscolumns.xtype = systypes.type AND " _
    & "syscolumns.id = " & rs("ID")
' open a second recordset
rs2.Open sql2, cn, adOpenStatic, adLockReadOnly, adCmdText
' Assign to a second grid    
Set DataGrid2.DataSource = rs2
DataGrid2.Refresh
Francesco Balena
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date