Getting MS SQL Database size using ColdFusion

March 25, 2008

Someone asked how to retrieve the size of all MS SQL Server databases using Coldfusion. There is a handy built in stored procedure called sp_spaceused which will do the job for you.

<cffunction name="GetAllDatasources" returntype="struct" output="false"
hint="I get a structure of all the available datasources.">
  <cfset var factory = 0 />
  <cfset var dsService = 0 />
  <cfset var stDatasources = 0 />

  <!--- Get CF "factory" --->
  <cfobject action="CREATE" type="JAVA" class="coldfusion.server.ServiceFactory" name="factory" />
  <!--- Get datasource service --->
  <cfset dsService=factory.getDataSourceService() />
   
  <cfset stDatasources = dsService.getDatasources() />
   
  <cfreturn stDatasources />
</cffunction>

<cfloop collection="#GetAllDatasources()#" item="item">
  <cftry>
    <cfstoredproc procedure="sp_spaceused" datasource="#item#">
      <cfprocresult name="qryResult" />
    </cfstoredproc>
    <cfdump var="#qryResult#" />
    <cfcatch>Not MS SQL Server</cfcatch>
  </cftry> 

</cfloop>

No comments

Leave a comment

If you found this post useful, interesting or just plain wrong, let me know - I like feedback :)

Please note: If you haven't commented before, then your comments will be moderated before they are displayed.