Agha Usman

Lives in Karachi (Pakistan) and work for Ciber Strategies

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.

Posted: Mar 09 2009, 06:54 PM by aghausman12 | with 6 comment(s) |
Filed under: , ,

Comments

Get Column name From Stored Procedure - Agha Usman said:

Pingback from  Get Column name From Stored Procedure - Agha Usman

# March 9, 2009 8:59 AM

Ben said:

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

# March 11, 2009 11:34 AM

aghausman12 said:

INFORMATION_SCHEMA.ROUTINE_COLUMNS is for table valued functions

# March 12, 2009 6:39 AM

Ben said:

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;

# March 12, 2009 1:05 PM

reader said:

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)

# May 7, 2009 6:23 PM

hackindabox said:

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

       }

# June 24, 2009 5:37 PM
Leave a Comment

(required) 

(required) 

(optional)

(required)