Hi, you can get most of that stuff through straight sql against the schema stuff (including the ddl of stored procs and views)
select * from sysobjects then sp_help eg for a table <cfstoredproc procedure="sp_help" datasource="#attributes.datasource#" returncode="Yes" dbname="#attributes.dbname#"> <cfprocparam type="In" cfsqltype="CF_SQL_CHAR" value="#attributes.table#" maxlength="100" null="No"> <cfprocresult name="qName" resultset="1"> <cfprocresult name="qParameters" resultset="2"> <cfprocresult name="qKeys" resultset="7"> </cfstoredproc> etc "Theo Galanakis" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > > I have written a VB com dll to extract a database schema including all > tables, stored procs, views etc.. I call this com object through <cf_object> > and it works as needed. > > However I have a query.... I have attempted to call the same methods in the > SQLDMO class directly in cold fusion, i.e no need to write the VB dll > directly to access the SQLDMO. > > Here is the Vb code which works fine... > > Public Function generateScript(server As String, database As String, > username As String, password As String) As String > > Dim SQLServer As New SQLDMO.SQLServer > > ' SQL Server security > If username = "(local)" Then > SQLServer.LoginSecure = True > SQLServer.Connect "(local)" > Else > SQLServer.Connect server, username, password > End If > > Dim tbl As SQLDMO.Table > Dim sp As SQLDMO.StoredProcedure > Dim view As SQLDMO.view > > ' Get a reference to the selected database > Set SQLDatabase = SQLServer.Databases(database) > > Dim param As SQLDMO_SCRIPT_TYPE > > Dim Script As String > > param = SQLDMOScript_Default Or SQLDMOScript_Indexes Or > SQLDMOScript_Triggers Or SQLDMOScript_DatabasePermissions > > 'Generate all the tables > For Each tbl In SQLDatabase.Tables > If Not tbl.SystemObject Then > Script = Script & tbl.Script(param) > End If > Next > > param = SQLDMOScript_Default > > 'Generate all the stored procedures > For Each sp In SQLDatabase.StoredProcedures > If Not sp.SystemObject Then > Script = Script & sp.Script(param) > End If > Next > > param = SQLDMOScript_Default > > 'Generate all the Views > For Each view In SQLDatabase.Views > If Not view.SystemObject Then > Script = Script & view.Script(param) > End If > Next > > 'Clean Up > SQLServer.DisConnect > Set SQLServer = Nothing > > generateScript = Script > End Function > > > > > > > > I wanted to replicate this in Cold Fusion, however I was not sure how to > defined something like this ... Dim tbl As SQLDMO.Table > > > Here is my cold Fusion code so far .... > > <cfobject type="COM" name="SQLServer" class="SQLDMO.SQLServer" > action="CREATE" context="LOCAL"> > <cfset SQLServer.Connect("SQLSERVER","(local)","")> > <cfset SQLDatabase = SQLServer.Databases()> > > <cfobject type="COM" name="tbl" class="SQLDMO.Tables" action="CREATE" > context="LOCAL"> > > <cfloop index="i" from="1" to="#tbl.Count#"> > <cfdump var="#tbl.Script(4)#"><br> > </cfloop> > > > It throws an error when attempting to call the SQLDMO.Tables object... In > the VB code it does not instantiate the SQLDMO.Tables as a new object but > rather just assigns a variable.. Dim tbl As SQLDMO.Table > > > Finally my question is how do you call the SQLDMO.Tables method, If you > cannot instantiate it? > > Ie is there a way to do this.. > > Something like this... <cfparam name="tbl" type="SQLDMO.Table"> > > > > > This message may contain privileged or confidential information and is > intended only for the individual named. If you are not the named addressee > you should not disclose, disseminate, distribute or copy this e-mail. If you > have received this e-mail by mistake please notify the sender immediately by > e-mail and delete this e-mail from your system. You should rely on your own > virus checking programmes and procedures for checking any attachments. > Please advise us if you wish your name and e-mail address to be removed from > our database. > > > --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004
