Get text of User Defined Functions?

[Lauren Quantrell]

I use the code below to generate a text file containing every stored
procedure and view in my SQL Server backend database, but I can't
figure out how to generate text of the User Defined Functions.

I see I can reference: SQLDMO.UserDefinedFunction
But can't find any way to reference dbs.<user defined functions>

Any help is greatly appreciated.
lq
Sub OutputDBQueries(myPath as String)

' In VB 6. Set a reference to Microsoft SQLDMO Object Library

Dim objSQLServer As New SQLDMO.SQLServer
Dim dbs As New SQLDMO.Database
Dim sp As SQLDMO.StoredProcedure
Dim v As SQLDMO.View
Dim sptext As String

objSQLServer.Connect <Servername>, <Username>, <Password>
Set dbs = objSQLServer.Databases(<Databasename>)

Open myPath For Output As #1

'>go through all stored procedures:
For Each sp In dbs.StoredProcedures

sptext = sp.Text

Print #1, sptext & _
vbCrLf & vbCrLf & vbCrLf & _

"************************************************* *****************************"
& _
vbCrLf & vbCrLf & vbCrLf
Next

'>go through all views:
For Each v In dbs.Views

sptext = v.Text

Print #1, sptext & _
vbCrLf & vbCrLf & vbCrLf & _

"************************************************* *****************************"
& _
vbCrLf & vbCrLf & vbCrLf
Next

MsgBox "The export is completed." & vbCrLf & vbCrLf & _
"Your export file has been created in: " & vbCrLf & _
myPath, vbInformation, "Export Completed"

End Sub

[Lauren Quantrell]

Lyle,
Excellent. Thanks.
lq

Lyle Fairfield wrote:

Lauren Quantrell wrote:

I use the code below to generate a text file containing every stored
procedure and view in my SQL Server backend database, but I can't
figure out how to generate text of the User Defined Functions.

I see I can reference: SQLDMO.UserDefinedFunction
But can't find any way to reference dbs.<user defined functions>

Any help is greatly appreciated.
lq
Sub OutputDBQueries(myPath as String)

' In VB 6. Set a reference to Microsoft SQLDMO Object Library

Dim objSQLServer As New SQLDMO.SQLServer
Dim dbs As New SQLDMO.Database
Dim sp As SQLDMO.StoredProcedure
Dim v As SQLDMO.View
Dim sptext As String

objSQLServer.Connect <Servername>, <Username>, <Password>
Set dbs = objSQLServer.Databases(<Databasename>)

Open myPath For Output As #1

'>go through all stored procedures:
For Each sp In dbs.StoredProcedures

sptext = sp.Text

Print #1, sptext & _
vbCrLf & vbCrLf & vbCrLf & _

"************************************************* *****************************"
& _
vbCrLf & vbCrLf & vbCrLf
Next

'>go through all views:
For Each v In dbs.Views

sptext = v.Text

Print #1, sptext & _
vbCrLf & vbCrLf & vbCrLf & _

"************************************************* *****************************"
& _
vbCrLf & vbCrLf & vbCrLf
Next

MsgBox "The export is completed." & vbCrLf & vbCrLf & _
"Your export file has been created in: " & vbCrLf & _
myPath, vbInformation, "Export Completed"

End Sub

Air Code:

Dim c As ADODB.Connection
Dim s As String
Set c = New ADODB.Connection
c.Open "PROVIDER=SQLOLEDB.1;" _
& "INITIAL CATALOG=DB_51315;" _
& "DATA SOURCE=mssql04.discountasp.net;" _
& "USER ID=Whatever;" _
& "PASSWORD=YYSW"
s = "SELECT so.name, sc.text"
s = s & vbNewLine & "FROM SysComments sc"
s = s & vbNewLine & "JOIN SysObjects so"
s = s & vbNewLine & "ON sc.ID = so.ID"
s = s & vbNewLine & "WHERE so.xtype='FN'"
s = s & vbNewLine & "ORDER BY so.Name"
Debug.Print c.Execute(s).GetString(adClipString, , , vbNewLine)

[Terry Kreft]

Because you are using a SQLDMO.Database object when you should be using a
SQLDMO.Database2 object.

So your procedure should be somthing like

Sub OutputDBQueries(myPath As String)
Dim strSep As String
Dim objSQLServer As New SQLDMO.SQLServer
Dim dbs As New SQLDMO.Database2
Dim sp As SQLDMO.StoredProcedure
Dim v As SQLDMO.View
Dim f As SQLDMO.UserDefinedFunction

Dim sptext As String

strSep = vbCrLf & vbCrLf & vbCrLf _
& String(78, "*") _
& vbCrLf & vbCrLf & vbCrLf

objSQLServer.Connect <Servername>, <Username>, <Password>
Set dbs = objSQLServer.Databases(<Databasename>)

Open myPath For Output As #1

'>go through all stored procedures:
For Each sp In dbs.StoredProcedures
sptext = sp.Text
Print #1, sptext & strSep
Next

'>go through all views:
For Each v In dbs.Views
sptext = v.Text
Print #1, sptext; strSep
Next

For Each f In dbs.UserDefinedFunctions
sptext = f.Text
Print #1, sptext; strSep
Next

MsgBox "The export is completed." & vbCrLf & vbCrLf & _
"Your export file has been created in: " & vbCrLf & _
myPath, vbInformation, "Export Completed"

End Sub

--

Terry Kreft
"Lauren Quantrell" <la*************@hotmail.comwrote in message
news:11**********************@75g2000cwc.googlegro ups.com...

I use the code below to generate a text file containing every stored
procedure and view in my SQL Server backend database, but I can't
figure out how to generate text of the User Defined Functions.

I see I can reference: SQLDMO.UserDefinedFunction
But can't find any way to reference dbs.<user defined functions>

Any help is greatly appreciated.
lq
Sub OutputDBQueries(myPath as String)

' In VB 6. Set a reference to Microsoft SQLDMO Object Library

Dim objSQLServer As New SQLDMO.SQLServer
Dim dbs As New SQLDMO.Database
Dim sp As SQLDMO.StoredProcedure
Dim v As SQLDMO.View
Dim sptext As String

objSQLServer.Connect <Servername>, <Username>, <Password>
Set dbs = objSQLServer.Databases(<Databasename>)

Open myPath For Output As #1

'>go through all stored procedures:
For Each sp In dbs.StoredProcedures

sptext = sp.Text

Print #1, sptext & _
vbCrLf & vbCrLf & vbCrLf & _

"************************************************* **************************
***"

& _
vbCrLf & vbCrLf & vbCrLf
Next

'>go through all views:
For Each v In dbs.Views

sptext = v.Text

Print #1, sptext & _
vbCrLf & vbCrLf & vbCrLf & _

"************************************************* **************************
***"

& _
vbCrLf & vbCrLf & vbCrLf
Next

MsgBox "The export is completed." & vbCrLf & vbCrLf & _
"Your export file has been created in: " & vbCrLf & _
myPath, vbInformation, "Export Completed"

End Sub

[Lauren Quantrell]

Thanks Terry,
This is very nice and neat now.
lq

Terry Kreft wrote:

Because you are using a SQLDMO.Database object when you should be using a
SQLDMO.Database2 object.

So your procedure should be somthing like

Sub OutputDBQueries(myPath As String)
Dim strSep As String
Dim objSQLServer As New SQLDMO.SQLServer
Dim dbs As New SQLDMO.Database2
Dim sp As SQLDMO.StoredProcedure
Dim v As SQLDMO.View
Dim f As SQLDMO.UserDefinedFunction

Dim sptext As String

strSep = vbCrLf & vbCrLf & vbCrLf _
& String(78, "*") _
& vbCrLf & vbCrLf & vbCrLf

objSQLServer.Connect <Servername>, <Username>, <Password>
Set dbs = objSQLServer.Databases(<Databasename>)

Open myPath For Output As #1

'>go through all stored procedures:
For Each sp In dbs.StoredProcedures
sptext = sp.Text
Print #1, sptext & strSep
Next

'>go through all views:
For Each v In dbs.Views
sptext = v.Text
Print #1, sptext; strSep
Next

For Each f In dbs.UserDefinedFunctions
sptext = f.Text
Print #1, sptext; strSep
Next

MsgBox "The export is completed." & vbCrLf & vbCrLf & _
"Your export file has been created in: " & vbCrLf & _
myPath, vbInformation, "Export Completed"

End Sub

--

Terry Kreft
"Lauren Quantrell" <la*************@hotmail.comwrote in message
news:11**********************@75g2000cwc.googlegro ups.com...

I use the code below to generate a text file containing every stored
procedure and view in my SQL Server backend database, but I can't
figure out how to generate text of the User Defined Functions.

I see I can reference: SQLDMO.UserDefinedFunction
But can't find any way to reference dbs.<user defined functions>

Any help is greatly appreciated.
lq
Sub OutputDBQueries(myPath as String)

' In VB 6. Set a reference to Microsoft SQLDMO Object Library

Dim objSQLServer As New SQLDMO.SQLServer
Dim dbs As New SQLDMO.Database
Dim sp As SQLDMO.StoredProcedure
Dim v As SQLDMO.View
Dim sptext As String

objSQLServer.Connect <Servername>, <Username>, <Password>
Set dbs = objSQLServer.Databases(<Databasename>)

Open myPath For Output As #1

'>go through all stored procedures:
For Each sp In dbs.StoredProcedures

sptext = sp.Text

Print #1, sptext & _
vbCrLf & vbCrLf & vbCrLf & _

"************************************************* **************************
***"

& _
vbCrLf & vbCrLf & vbCrLf
Next

'>go through all views:
For Each v In dbs.Views

sptext = v.Text

Print #1, sptext & _
vbCrLf & vbCrLf & vbCrLf & _

"************************************************* **************************
***"

& _
vbCrLf & vbCrLf & vbCrLf
Next

MsgBox "The export is completed." & vbCrLf & vbCrLf & _
"Your export file has been created in: " & vbCrLf & _
myPath, vbInformation, "Export Completed"

End Sub