SMO and Sproc Class Generation

Question

I am attempting to build a DAL generator for myself. While most of it has gone perfectly, I am a bit stuck when it comes to my sprocs.

In my database, I have a set # of sprocs that are simple select statements, such as:

ALTER PROCEDURE [Details].[Page]

    @ID         BigInt

AS
BEGIN
    SET NOCOUNT ON;

    Select PageID, SiteID, TemplateID, Parent, Updated, Created, Settings, PublishDate, Publish, Page, LinkAlias, Keywords, Description, HomePage, Title
    From Selects.Pages
    Where PageID = @ID

END

I am able to get the schema, the name, and the parameters via utilizing Imports Microsoft.SqlServer.Management.Smo

How can I get the columns returned from the procedure though?

Here is my code:

Private Sub GrabProcedures()
    Dim _Db As Database = Srv.Databases(DBName)
    Dim _Procs As ParallelQuery = _Db.StoredProcedures.AsParallel()
    Dim _i As Integer = 0
    For Each proc As StoredProcedure In _Procs
        If Not proc.IsSystemObject Then
            _i += 1

            _Procedures.Add(New ProcedureTyping() With {
                        .ID = _i,
                        .Name = proc.Name,
                        .Schema = proc.Schema,
                        .Parameters = ProcessParameters(proc.Parameters),
                        .Include = True,
                        .GenerateSelect = False})
        End If
    Next
    _SPCount = _Procedures.Count
End Sub

Private Function ProcessParameters(_params As StoredProcedureParameterCollection) As List(Of ParameterTyping)
    Dim _L As New List(Of ParameterTyping)
    Dim _p As Integer = 0
    For Each param As StoredProcedureParameter In _params
        _p += 1
        _L.Add(New ParameterTyping() With {
               .ID = _p,
               .Name = param.Name,
               .Type = param.DataType.SqlDataType,
               .Length = param.DataType.MaximumLength,
               .OutParam = param.IsOutputParameter,
               .DefaultValue = param.DefaultValue})
    Next
    Return _L
End Function

Partial Public Class ProcedureTyping
    Public Property ID As Integer
    Public Property Name As String
    Public Property Schema As String
    Public Property Parameters As List(Of ParameterTyping)
    Public Property Include As Boolean
    Public Property GenerateSelect As Boolean
End Class

Partial Public Class ParameterTyping
    Public Property ID As Integer
    Public Property Name As String
    Public Property Type As SqlDataType
    Public Property Length As Integer
    Public Property OutParam As Boolean
    Public Property DefaultValue As String
End Class

Please assume that _Db and _Procs are populating correctly (since the are).

Edit - Attempt to get Sproc Columns via sp_describe_first_result_set

Private Function ProcessProcColumns(ByVal _Name As String, ByVal _Schema As String) As List(Of ColumnTyping)
    Dim _sql As String = "Exec sp_describe_first_result_set N'" & If(_Schema.Length > 0, _Schema & ".", "") & _Name & "'"
    Dim _rs As SqlDataReader = Srv.ConnectionContext.ExecuteReader(_sql)
    Dim _i As Integer = 0
    If _rs IsNot Nothing Then
        While _rs.Read()
            _i += 1
            _ProcColumns.Add(New ColumnTyping() With {
                                .ID = _i,
                                .Name = _rs(2),
                                .Type = _rs(5),
                                .Length = _rs(6),
                                .DefaultValue = String.Empty,
                                .Precision = _rs(7),
                                .Scale = _rs(8),
                                .IsPrimary = _rs(27)})
        End While
    End If
End Function

Answer 1

One of the new functions in SQL Server 2012 is sys.sp_describe_first_result_set which returns metadata about the first result set.

I don't think any similar feature is exposed via SMOs but if your SPs are just simple SELECT statements you could try to parse the TextBody.