How to get the query of a VIEW on SQL Server - without CREATE VIEW part

Question

Microsoft lists 3 different ways to get the definition of a view in SQL Server (also see this StackOverflow question), f.e.

SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('MY.TEST_VIEW');

But they all include CREATE VIEW in the output (which is usually fine) and I need the exact query, i.e. for

CREATE VIEW [MY].[TEST_VIEW] (COL /*unfortunately some comment containing `as` and brackets :) */)
AS
   SELECT 1

I only want

   SELECT 1

While it's always possible to write a small-ish parser to find the correct AS in the view definition, I'd rather prefer something akin to Oracle's DBA_VIEWS:

select TEXT from DBA_VIEWS where OWNER = 'MY' and VIEW_NAME = 'TEST_VIEW'

UPDATE Answer from Dan Guzman translated into vb.net

Imports System.Runtime.Serialization

Imports Microsoft.SqlServer.TransactSql.ScriptDom

<CLSCompliant(False)>
Public Class ViewSelectStatementExtractor : Inherits TSqlFragmentVisitor
    Private SelectStatement As String

    'visitor method invoked for each CREATE VIEW statement in script
    Public Overrides Sub Visit(fragment As CreateViewStatement)

        Dim selectStatementFragment = fragment.SelectStatement
        'extract SELECT statement text from tokens
        Using sw = New IO.StringWriter
            For i = selectStatementFragment.FirstTokenIndex To selectStatementFragment.LastTokenIndex
                sw.Write(selectStatementFragment.ScriptTokenStream(i).Text)
            Next
            Me.SelectStatement = sw.ToString()
        End Using
    End Sub

    'Get view SELECT statement Using T-SQL script dom parser and visitor
    Shared Function Extract(createViewStatement As String) As String
        Dim parser = New Microsoft.SqlServer.TransactSql.ScriptDom.TSql150Parser(True)
        Dim parseErrors As IList(Of ParseError) = New List(Of ParseError)
        Dim fragment As TSqlFragment
        Using stringReader = New IO.StringReader(createViewStatement)
            fragment = parser.Parse(stringReader, parseErrors)
        End Using
        If (parseErrors.Count > 0) Then
            Dim errorStrings = From e In parseErrors Select $"{e.Line},{e.Column}: {e.Message}"
            Throw New ParseTSqlException($"{parseErrors.Count} parsing errors: {vbNewLine}{String.Join(vbNewLine, errorStrings)}")
        End If

        Dim visitor = New ViewSelectStatementExtractor
        fragment.Accept(visitor)
        Return visitor.SelectStatement
    End Function
End Class

<Serializable>
Public Class ParseTSqlException
    Inherits Exception

    Public Sub New()
    End Sub

    Public Sub New(message As String)
        MyBase.New(message)
    End Sub

    Public Sub New(message As String, innerException As Exception)
        MyBase.New(message, innerException)
    End Sub

    Protected Sub New(info As SerializationInfo, context As StreamingContext)
        MyBase.New(info, context)
    End Sub
End Class

Answer 1

Although you could extract the SELECT query from the CREATE VIEW definition using T-SQL, that method can be unreliable when the view definition includes comments and other unexpected constructs. Consider using the the The T-SQL script DOM library instead.

Below is a .NET-ish PowerShell script example that uses a visitor for the task gleaned from this article you can extend as desired. This uses the Microsoft.SqlServer.TransactSql.ScriptDom.dll assembly from a local SSMS installation but you may want to use the assembly from the DacFx NuGet package instead to avoid the SSMS dependency and provide more control over the specific version.

Because the PowerShell visitor class implementation derives from a type defined in the external Microsoft.SqlServer.TransactSql.ScriptDom.dll assembly, it is necessary to first load the assembly into the app domain so the PS visitor class can compile. I use a wrapper script to load the type and dot source the main script to work around this issue.

Extract-ViewSelectSttatementWrapper.ps1:

Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Extensions\Application\Microsoft.SqlServer.TransactSql.ScriptDom.dll"
. "C:\Users\dguzman.GUZMAN1\Documents\Powershell\Extract-ViewSelectStatement.ps1"

Extract-ViewSelectStatement.ps1:

class ViewVisitor: Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor {

    [String]$SelectStatement

    # visitor method invoked for each CREATE VIEW statement in script
    [void]Visit ([Microsoft.SqlServer.TransactSql.ScriptDom.CreateViewStatement] $fragment) {

        $selectStatementFragment = $fragment.SelectStatement
        # extract SELECT statement text from tokens
        $sw = New-Object System.IO.StringWriter
        for($i = $selectStatementFragment.FirstTokenIndex; $i -le $selectStatementFragment.LastTokenIndex; ++$i) {
            $sw.Write($selectStatementFragment.ScriptTokenStream[$i].Text)
        }
        $this.SelectStatement = $sw.ToString()
        $sw.Close()

    }

    # get view select statement using T-SQL script dom parser and visitor
    static [String] getSelectStatement($createViewStatement) {

        $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql150Parser($true)
        $parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]
        $stringReader = New-Object System.IO.StringReader($createViewStatement)

        $fragment = $parser.Parse($stringReader, [ref]$parseErrors)
        if($parseErrors.Count -gt 0) {
            throw "$($parseErrors.Count) parsing errors: $(($parseErrors | ConvertTo-Json))"
        }

        $visitor = [ViewVisitor]::new()

        $fragment.Accept($visitor)

        return $visitor.SelectStatement

    }
}

Function Get-ViewSelectStatement ($connectionString, $viewNAme) {

    $query = @"
    SELECT definition
    FROM sys.sql_modules
    WHERE object_id = OBJECT_ID(@ViewName);
"@

    $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
    $connection.Open()
    $command = New-Object System.Data.SqlClient.SqlCommand($query, $connection)
    [void]$command.Parameters.Add("@ViewName", [System.Data.SqlDbType]::NVarChar, 261)
    $command.Parameters["@ViewName"].Value = $viewName
    $createViewStatement = $command.ExecuteScalar()
    $connection.Close()

    return [ViewVisitor]::getSelectStatement($createViewStatement)


}


# ############
# ### MAIN ###
# ############

$connectionString = "Data Source=YourServer;Initial Catalog=YourDatabase;Integrated Security=SSPI"

try {

    Get-ViewSelectStatement -connectionString $connectionString -viewName "MY.TEST_VIEW"

}
catch {

    throw

}