1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44
|
Public Const SERVER_N = "LZ2"
Public Const SQLOLEDBconn = "Provider=SQLOLEDB" & _
";DATA SOURCE=" & SERVER_N & _
";INITIAL CATALOG=MOP" & _
";Trusted_Connection=Yes" ' authentification NT
' ";USER ID=MOPREAD" & _
' ";PASSWORD=xxxxxxx"
Function LireSQLview(ViewName As String, Optional Owner As String = "*")
Dim oConn As ADODB.Connection, oRS As ADODB.Recordset
Dim strSQL As String, strView As String, p As Long
strSQL = "SELECT VIEW_DEFINITION text, TABLE_SCHEMA owner" & vbCrLf & _
"FROM INFORMATION_SCHEMA.VIEWS" & vbCrLf & _
"WHERE (TABLE_NAME = '" & ViewName & "')"
If Owner <> "*" Then
strSQL = strSQL & " AND (TABLE_SCHEMA='" & Owner & "')"
End If
Set oConn = New ADODB.Connection
oConn.ConnectionString = SQLOLEDBconn ' Const connexion déclarée plus haut
oConn.Open
Set oRS = New ADODB.Recordset
oRS.CursorLocation = adUseClient
oRS.Open strSQL, oConn, adOpenForwardOnly, adLockOptimistic, adCmdText
If Not oRS.EOF Then
strView = oRS!Text
p = InStr(1, oRS!Text, vbCrLf & "AS" & vbCrLf) + 6
If p > 7 Then
strView = Mid(oRS!Text, p, Len(oRS!Text) - p - 1)
End If
Else
strView = "NON TROUVE"
End If
oRS.Close
Set oRS = Nothing
Set oConn = Nothing
LireSQLview = strView
End Function |
Partager