Geeks With Blogs

News

Agha Usman Ahmed JILAWATAN

The requirement of the day is to extract the name of the columns returned by procedures. Stored Procedures are dynamic that is why we need to create a function that takes Stored Procedure name as parameter and return the column names in string. So here is the quick snippet for that

   1: Public Shared Function getMetaData(ByVal spName As String) As String()
   2:        Dim sqlCon As New SqlConnection(ConfigurationManager.ConnectionStrings("lmString").ConnectionString)
   3:        sqlCon.Open()
   4:  
   5:        Dim sqlCmd As New SqlCommand("sp_helptext " + spName, sqlCon)
   6:        Dim sqlDataAdapter As New SqlDataAdapter(sqlCmd)
   7:        Dim dt As New DataTable
   8:        Dim strTempQuery As String = String.Empty
   9:        Dim strColumns As String()
  10:        Dim strCol As String = String.Empty
  11:  
  12:        sqlDataAdapter.Fill(dt)
  13:        If dt.Rows.Count > 0 Then
  14:            For Each dr As DataRow In dt.Rows
  15:                strTempQuery += dr.Item(0)
  16:            Next
  17:        End If
  18:  
  19:        If Not strTempQuery = "" Then
  20:  
  21:            'Dim objRegex As New Regex("select([^<]*)from")
  22:  
  23:  
  24:            Dim objMatches As MatchCollection = Regex.Matches(strTempQuery, "select([^<]*)from", RegexOptions.IgnoreCase)
  25:  
  26:            For Each mymatch As Match In objMatches
  27:                strCol += mymatch.Groups(1).Value
  28:            Next
  29:  
  30:            If Not strCol = "" Then
  31:                strColumns = strCol.Split(",")
  32:                For a As Integer = 0 To strColumns.Length - 1
  33:                    strColumns(a) = strColumns(a).Trim()
  34:                Next
  35:            End If
  36:        End If
  37:        Return strColumns
  38:    End Function

 

Restriction : Though, we have achieved the target, but since we have used sp_helptext to extract the Stored Procedure data that is why it is not possible to process encrypted stored procedure.

Will make it more better in the future to accommodate all type of Stored Procedures.

Posted on Monday, March 9, 2009 5:55 PM Vb.net , .net , SQL Server | Back to top


Comments on this post: Get Column name From Stored Procedure

# re: Get Column name From Stored Procedure
Requesting Gravatar...
Accha artice post kya hai...

I like u Usman.
Left by Farhan ulHaque Farooqui CSI on Sep 05, 2010 4:49 PM

Your comment:
 (will show your gravatar)


Copyright © Agha Usman Ahmed | Powered by: GeeksWithBlogs.net