Getting MS SQL Database size using ColdFusion

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.

You can view the code to do this here

Using CharIndex to get a SubString

If you need to extract part of a string based on the index of a character in SQL, then you can use the CharIndex function and the Substring function. This works the same as the Find & Mid functions in ColdFusion and IndexOf in JavaScript.

For example  the order_deliverycountry field holds values in the format ‘UK|United Kingdom’. To get the ‘UK’ part use the following code:

select SubString([Orders].order_deliverycountry, 1, CharIndex(’|', Orders.order_deliverycountry) - 1) as CountryCode
from [Countries]

SQL Server Express 2005 and ColdFusion 8

ColdFusion does not connect to the default install of SQL Server Express 2005. After much swearing I discovered that you need to enable TCP/IP (which is disabled by default).

To enable it go to Start -> Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager. In the SQL Server 2005 Network Configuration, click on Protocols for SQLEXPRESS. Click on TCP/IP, and tick the box to enable it. Note: You will need to restart the SQL Server Service, which you can do from Services in your Control Panel.

If you have a firewall, then you need to allow SQL Server. It is a good idea to lock TCP/IP connections down to one port for security. SQL Server 2000 used port 1433. To set the port, clear the value for “TCP Dynamic Ports” and enter the port you want to use in the “TcpPort” box. Note: you will need to use the same port in the ColdFusion CFIDE administrator.

Sorting alpha and numeric data in the same field

The problem I had was the client was entering some room names as numbers and others as a number with a character at the end. When MS SQL server sorts the column it does it by the field data type (in this case nvarchar), which meant you would get this:

108, 109, 11, 110, 111D, 112D

To get a list like this:

11, 108, 109, 110, 111D, 112D

I modified the query using the IsNumeric function in MS SQL Server to this:

Select [room_name],
  Case When IsNumeric(room_name) = 1 Then Cast(room_name as int) Else 9999999 End As room_sort
from [Rooms]
order by room_sort

SQL server table schema

SELECT Table_Name
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Type = ‘BASE TABLE’
AND Table_Name <> ‘dtproperties’