Auto Documenting SQL Server 2005 Stored Procedures
One of the features I like most about ColdFusion 8 is the cfdbinfo tag. The cfdbinfo tag allows developers to view database meta information such as tables, columns and data types. Another nice thing about the cfdbinfo tag is that it will work with most RDBMS servers.
Each database server, whether it is Oracle, MySql or MS SQL Server, all have different ways of getting this meta information. CFDBINFO gives ColdFusion developers a nice level of abstraction from these different ways of accessing meta information.
I am currently trying to document all of the stored procedures I wrote for a project I just finished. I decided I wanted to try to automate this task, so I wrote ColdFusion script to list my stored procedures. The following code shows how you can use the cfdbinfo tag to list all of your stored procedures.
<cfdump var="#myprocs#" />
This may be enough information for some users, but I also wanted to display all of the input and output parameters as well as the code in the procedure as well.
I work with SQL Server 2005 as my primary database engine. All of the database meta information can be accessed through system views known as INFORMATION_SCHEMAs. There are two views that I used to get the information about my procedures that I wanted. They are the INFORMATION_SCHEMA.ROUTINES and INFORMATION_SCHEMA.PARAMETERS views.
I used the following query to get the meta information that I needed for my documentation;
SELECT r.SPECIFIC_SCHEMA, r.SPECIFIC_NAME, r.ROUTINE_DEFINITION,
p.ORDINAL_POSITION, p.PARAMETER_MODE, p.PARAMETER_NAME, p.DATA_TYPE
FROM INFORMATION_SCHEMA.ROUTINES r
JOIN INFORMATION_SCHEMA.PARAMETERS p
ON r.SPECIFIC_SCHEMA = p.SPECIFIC_SCHEMA
AND r.SPECIFIC_NAME = p.SPECIFIC_NAME
WHERE r.ROUTINE_TYPE = 'PROCEDURE'
ORDER BY r.SPECIFIC_SCHEMA, r.SPECIFIC_NAME, p.ORDINAL_POSITION
</cfquery>
I then used the following code to display the stored procedures in my database;
<h2>#SPECIFIC_SCHEMA#.#SPECIFIC_NAME#</h2>
<p>The parameters and data types for the #SPECIFIC_SCHEMA#.#SPECIFIC_NAME# procedure are as follows;</p>
<ol>
<cfoutput>
<li>#PARAMETER_MODE#, #PARAMETER_NAME#, #DATA_TYPE#</li>
</cfoutput>
</ol>
<p>Here is the transact-sql for the #SPECIFIC_SCHEMA#.#SPECIFIC_NAME# procedure.</p>
<div class="solid">
<pre>#ROUTINE_DEFINITION#</pre><br /><br />
</div>
<br />
</cfoutput>
I need to create a we based version such that, if one selects the database type, say oracle,
then it should display all the stored procedures in the database.
If any stored procedure is selected, then the next window should display the input parameters so that the user can enter and hit submit.
hitting submit should execute the stored procedure and simply results.
i am pretty new to Coldfusion.. Any good suggestions on this...
Thank you.