Terry Kreft was nice enough to put this code together that creates a
text file of all the SP's, Views and User Defined Functions in a
project.
Is there a way to do the same for all of the tables, getting the table
names with all of their columns and column specs?
lq
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