Generate SQL Tool
SQL Server Enterprise Manager has the ability to script out database objects to a file so you can recreate them at a later date. I wanted a way to be able to do the same thing in coldfusion and had some time on my hands. The goal is to start with a table and generate a create script to recreate it with indexes, constraints, primary keys, foreign keys and any dependent objects. Dependent objects might be tables referred to in foreign keys or triggers and the tables they reference.
First we need a way to select a table so save the following code for sp_help.cfm.
<CFSET request.dsn="webdevel">
<CFSETTING showdebugoutput="No">
<CFOUTPUT>
<STRONG> Please pass a URL Param tblNm for the object you are trying to view.</STRONG>
<FORM name="getObject" method="get" action="">
Object
<INPUT type="text" name="tblNm">
<INPUT type="Submit" value="Go">
</FORM>
<CFIF not isdefined("url.tblNm")>
<CFPARAM name="url.tblNm" default="in_whitm_tbl">
</CFif>
<TITLE> Generate SQL Script for #url.tblNm#</TITLE>
<CF_QRY_SP_HELP tblnm="#url.tblNm#">
#results#
</CFOUTPUT>
Next we need to execute a series of SQL Server stored procedures to extract the relevant information and generate a sql script. Save the following as qry_sp_help.cfm
<CFsavecontent variable="caller.results">
<CFset reflist="">
<CFstoredproc datasource="#request.dsn#" procedure="sp_help">
<CFprocresult name = RS1 resultSet = 1>
<CFprocresult name = RS2 resultSet = 2>
<CFprocresult name = RS3 resultSet = 3>
<CFprocresult name = RS4 resultSet = 4>
<CFprocresult name = RS5 resultSet = 5>
<CFprocresult name = RS6 resultSet = 6>
<CFprocresult name = RS7 resultSet = 7>
<CFprocresult name = RS8 resultSet = 8>
<CFprocparam cfsqltype="CF_SQL_VARCHAR"
dbvarname="@objname" null="No" type="In"
value="#attributes.tblNm#">
</CFstoredproc>
<!--- Find all associated triggers --->
<CFstoredproc datasource="#request.dsn#" procedure="sp_helptrigger">
<CFprocresult name = triggers resultSet = 1>
<CFprocparam cfsqltype="CF_SQL_VARCHAR"
dbvarname="@objname" null="No"
type="In" value="#attributes.tblNm#">
</CFstoredproc>
<CFoutput>
--Table #attributes.tblNm#<BR>
CREATE TABLE #rs1.owner#.#rs1.name# (<BR>
</CFoutput>
<CFoutput query="rs2">
#column_name# #type#
<CFif type contains "char">
(#length#)
</CFif>
<CFif nullable eq "no">
NOT NULL
<CFelse>
NULL
</CFif>
<CFif currentRow lt recordcount>
,<br>
</CFif>
</CFoutput>
)<BR>
GO<BR> <BR>
--indexes<BR>
<CFOUTPUT>
<CFloop query="rs6">
CREATE INDEX [#rs6.index_name#]
ON [#rs1.owner#].[#rs1.name#](#rs6.INDEX_KEYS#) <BR>
GO<BR>
</CFloop>
<BR>
--constraints<BR>
ALTER TABLE [#rs1.owner#].[#rs1.name#] ADD <BR>
<CFloop query="rs7">
<CFif rs7.constraint_type contains "DEFAULT" >
CONSTRAINT [#rs7.constraint_name#]
DEFAULT #rs7.constraint_keys#
FOR [#listgetat(rs7.constraint_type,4," ")#]
<CFif rs7.currentrow lt rs7.recordcount>
,
</CFif><BR>
<CFelseif rs7.constraint_type contains "FOREIGN KEY">
CONSTRAINT [#rs7.constraint_name#] FOREIGN KEY
(#rs7.constraint_keys#)
<CFelseif len(trim(rs7.constraint_type)) eq 0>
#rs7.constraint_keys#
<CFset reflist=listappend(reflist,listgetat(rs7.constraint_keys,2," "))>
<CFif rs7.currentrow lt rs7.recordcount>
,
</CFif><BR>
<CFelseif rs7.constraint_type contains "PRIMARY KEY">
<CFset primkey=rs7.constraint_keys>
CONSTRAINT [#rs7.constraint_name#]
#replacenocase(rs7.constraint_type,"(clustered)","CLUSTERED","ALL")#
(#rs7.constraint_keys#)
<CFif rs7.currentrow lt rs7.recordcount>
,
</CFif><BR>
</CFif>
</CFloop>
GO<BR>
<BR>
--triggers<BR>
<CFloop query="triggers">
<CFstoredproc datasource="webdevel" procedure="sp_helptext">
<CFprocresult name = triggertext resultSet = 1>
<CFprocparam cfsqltype="CF_SQL_VARCHAR"
dbvarname="@objname" null="No" type="In"
value="#triggers.trigger_name#">
</CFstoredproc>
<CFloop query="triggertext">
#triggertext.text#<BR>
</CFloop>
</CFloop>
<BR>
--dependent objects<BR>
<CFstoredproc datasource="#request.dsn#" procedure="sp_MSdependencies">
<CFprocresult name = otherObjects resultSet = 1>
<CFprocparam cfsqltype="CF_SQL_VARCHAR"
dbvarname="@objname" null="No" type="In" value="#attributes.tblNm#">
<CFprocparam cfsqltype="CF_SQL_VARCHAR" dbvarname="@objlist"
null="No" type="In" value="4607">
</CFstoredproc>
#reflist#<BR>
#valuelist(otherobjects.oObjName)#<BR>
<CFloop query="otherObjects">
<CFif reflist does not contain otherobjects.oObjName>
<CFset reflist=listappend(reflist,otherobjects.oObjName)>
</CFif>
</CFloop>
<CFloop list="#reflist#" index="lix">
<CF_qry_sp_help tblNm="#lix#">
#results#<BR>
</CFloop>
</CFoutput>
</CFsavecontent>