Retrieve the list of SQL Server stored procedure

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.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.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 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 " _    & " = " & rs("ID")' open a second recordsetrs2.Open sql2, cn, adOpenStatic, adLockReadOnly, adCmdText' Assign to a second grid    Set DataGrid2.DataSource = rs2DataGrid2.Refresh

Share the Post:
Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several