That is where sp_helptext comes in - get contents of proc unless compiled with encryption option
"Theo Galanakis" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > > Hi Elliot, > I'm familiar with sp_help and thankyou for the tip, I actually use > the comm Object to extract the entire table, stored procedure, view for > backup purposes formatted ready for a database rebuild, i.e simulating the > enterprise manager Generate SQL Script... functionality.. > > i.e sp_help produces the parameter inputs but not the entire stored > procedure scripted. > > The real question I guess is how do you effectively call the SQLDMO.Table in > cold fusion... it works in vb code below. > > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Elliot Russo > Sent: Thursday, 6 May 2004 10:49 AM > To: CFAussie Mailing List > Subject: [cfaussie] Re: calling SQLDMO Com experts... > > 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 > > 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
