Search code examples
coldfusion

Checking if a Column exists in Database table

I am using the following code to check if a Column exists, when not then create it.

<cfquery name="qSelect" datasource="#dbname#">
    select Top 0 * from Elements
</cfquery>

<cfif not ListFindNoCase(qSelect.columnlist,"idLang")>
   <cfquery name="qAlterTable" datasource="#dbname#" dbtype="ODBC">
      ALTER TABLE Elements ADD idLang varchar(200) NULL
   </cfquery>
</cfif>

Can someone suggest if there is better way to do this?

Solution

  • I would query the information_schema directly.

    SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTableName' 
AND COLUMN_NAME = 'idLang'

    If you're checking multiple column names you can change the = to an IN and then perform the same check you're doing now (listFindNoCASE). I don't know that either way is faster/better, but this is how I would do it.

    You don't specify a DBMS, but this will work for SQL Server and MySQL