Browse DevX
Sign up for e-mail newsletters from DevX

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



Building the Right Environment to Support AI, Machine Learning and Deep Learning

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
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
Francesco Balena
Comment and Contribute






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



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