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.

Published Monday, March 09, 2009 6:54 PM by aghausman12
Filed under: , ,

Comments

# Get Column name From Stored Procedure - Agha Usman

Monday, March 09, 2009 8:59 AM by Get Column name From Stored Procedure - Agha Usman

Pingback from  Get Column name From Stored Procedure - Agha Usman

# re: Get Column name From Stored Procedure

Wednesday, March 11, 2009 11:34 AM by Ben

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

# re: Get Column name From Stored Procedure

Thursday, March 12, 2009 6:39 AM by aghausman12

INFORMATION_SCHEMA.ROUTINE_COLUMNS is for table valued functions

# re: Get Column name From Stored Procedure

Thursday, March 12, 2009 1:05 PM by Ben

You are correct, I don't know why I didn't notice that.  

There is always sys.parameters in SQL 2005 or 2008.

msdn.microsoft.com/.../ms345522.aspx

SELECT SCHEMA_NAME(schema_id) AS schema_name

   ,o.name AS object_name

   ,o.type_desc

   ,p.parameter_id

   ,p.name AS parameter_name

   ,TYPE_NAME(p.user_type_id) AS parameter_type

   ,p.max_length

   ,p.precision

   ,p.scale

   ,p.is_output

FROM sys.objects AS o

INNER JOIN sys.parameters AS p ON o.object_id = p.object_id

--WHERE o.object_id = OBJECT_ID('<schema_name.object_name>')

ORDER BY schema_name, o.name, p.parameter_id;

# re: Get Column name From Stored Procedure

Thursday, May 07, 2009 6:23 PM by reader

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)

# re: Get Column name From Stored Procedure

Wednesday, June 24, 2009 5:37 PM by hackindabox

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 "";

       }

Leave a Comment

(required) 
(required) 
(optional)
(required)