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.ConnectionDim rs As New ADODB.RecordsetDim connString As String, sql As String' Open a connection connString = "Provider=SQLOLEDB;Data Source=P2;Initial Catalog=Pubs"cn.Open connString, "sa", ""' Open the recordsetsql = "Select * from sysobjects where type = 'P' and category = 0"rs.Open sql, cn, adOpenStatic, adLockReadOnly, adCmdText' Load the results in a DataGrid controlSet DataGrid1.DataSource = rsDataGrid1.RefreshThe 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 parametersDim rs2 As New ADODB.Recordset, sql2 As Stringsql2 = "SELECT syscolumns.Name, systypes.Name, syscolumns.length " & _ "FROM syscolumns, systypes " & "WHERE syscolumns.xtype = systypes.type AND " _ & "syscolumns.id = " & rs("ID")' open a second recordsetrs2.Open sql2, cn, adOpenStatic, adLockReadOnly, adCmdText' Assign to a second grid Set DataGrid2.DataSource = rs2DataGrid2.Refresh
