Get Column name From Stored Procedure

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.

4 Comments

  • If you are using MSSQL 2005 or 2008, you might take a look at the INFORMATION_SCHEMA collection.

    Specifically:
    SELECT *
    FROM INFORMATION_SCHEMA.ROUTINE_COLUMNS

  • INFORMATION_SCHEMA.ROUTINE_COLUMNS is for table valued functions

  • use this regular expression in the above code. It uses Look behind and Look ahead patterns. Before using it strip the hard returns from the input strings.

    (?<=select).*?(?=from)

  • You can also use the DataTable to get the column names.


    private string GetColumnNames(SqlConnection cConnection)
    {
    try
    {
    string sStoredProcName = "teststoredproc";
    string strTempQuery = string.Empty;
    SqlCommand sCommand = new SqlCommand(sStoredProcName, cConnection);
    SqlDataAdapter sAdapter = new SqlDataAdapter(sCommand);
    DataTable dt = new DataTable();
    sAdapter.Fill(dt);

    if (dt.Rows.Count > 0)
    {
    //read columns names from datatable column
    for (int i = 0; i < dt.Rows[0].Table.Columns.Count; i++)
    {
    strTempQuery += dt.Rows[0].Table.Columns[i].ColumnName + ",";
    }

    //string that holds the column names of stroed proceudre
    strTempQuery = strTempQuery.Substring(0, strTempQuery.LastIndexOf(","));
    }

    return strTempQuery + Environment.NewLine;
    }
    catch (Exception)
    {
    //do nothing
    }

    return "";
    }

Comments have been disabled for this content.