CODEWITHSUNDEEP
c#sql.netsql-servervb.net
Fame th
Fame th

Reputation: 1038

How to get sql code of VIEW in SQL server by programmatically in .NET

First, I can get base tables and columns of a View by using simple way like these. 

Private Function GetBasetableFromSql(ByVal viewTable As String) As Integer
    Dim dt As New DataTable
    Try
        Dim sqlConn As SqlConnection
        sqlConn = New SqlConnection("Data Source=xxx;Initial Catalog=xxx_MMS;User ID=xxx;Password=xxxx")
        sqlConn.Open()
        Dim ta As SqlDataAdapter
        Dim strSql As String 
         strSql = "SELECT DISTINCT VIEW_NAME,c.TABLE_NAME " + _ 
                   FROM    INFORMATION_SCHEMA.VIEW_COLUMN_USAGE AS cu JOIN    
                   INFORMATION_SCHEMA.COLUMNS AS c ON  c.TABLE_SCHEMA  = 
                   cu.TABLE_SCHEMA AND  c.TABLE_CATALOG = cu.TABLE_CATALOG AND     
                   c.TABLE_NAME    = cu.TABLE_NAME AND     c.COLUMN_NAME   = cu.COLUMN_NAME" & _
            " WHERE VIEW_NAME = '" & ViewTableName & "'"
        ta = New SqlDataAdapter(strSql, sqlConn)

        ta.Fill(dt)

        For Each dr As DataRow In dt.Rows
            dgvRootTable.Rows.Add(dr(0), dr(1))
        Next
        sqlConn.Close()

    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
    Return dt.Rows.Count
End Function

But some View was created by complicated query. So I need to get the sql code from a view before and use the way of regular expression (If there is another way.please tell me.) for get table and column from string of sql code. But I have no idea how to get sql code by programmatically?

Sameple View which was created by sql code enter image description here

If I can get Base Table and Column. I will create result in document like this

**View Table :** V_PO_SCRAP_NG_DTLS
[SCRAP_NG_DTLS].[TRAN_ID]
[SCRAP_NG_DTLS].[SEQ_NO]
[SCRAP_NG_DTLS].[CAUSE-OF-NG-JD]
[SCRAP_NG_DTLS].[CAUSE_OF_NG_DESCR]
[SCRAP_NG_DTLS].[NG_QTY]

Upvotes: 0

Views: 1300

Answers (1)

Abdul Ghaffar
Abdul Ghaffar

Reputation: 182

If you want to see the sql script of view you can use following command . you could use same command to get sql script of stored procedure, and press Ctrl+T to see the result in text format.

sp_helptext 'yourViewName or ProcedureName'

Upvotes: 5

Related Questions