Creating, Deleting and modifying database tables on the fly with ColdFusion.

This tutorial will demonstrate how to create tables in your database using your ColdFusion applications! Let's begin:

the Create table statement creates a new database table in the specify datasource.
(Please note : syntax varies from database to database. [ This code has been tested in Access and SQL Server 2000 ])

<!--- This will create the tables on the database --->
<cfquery name="qCreateTable"  datasource="MyDSN">
    CREATE TABLE Members (
                            member_id counter Primary Key,
                            member_fname varchar(255),
                            member_lname varchar(255),
                            member_age integer,
                            member_visits numeric,
                            CONSTRAINT ID PRIMARY KEY(ID)
                            )
</cfquery>

The next thing you need to learn is how to delete a table on the fly, you do this with the "DROP TABLE" command. The Drop statement deletes an existing table (including all data) in the specify datasource.

<!--- This will completely delete a table and all items in it --->
<cfquery name="qDeleteTable" datasource="MyDSN">
        DROP TABLE Members 
</cfquery>

The next step is to modify fields in a table with the ALTER TABLE statement:

<!--- This will alter a column in the table --->
<cfquery name="qAlterTable" datasource="MyDSN">
        ALTER TABLE Members
        ALTER COLUMN member_visits Long
</cfquery>

<!--- This will alter a table by deleting the column --->
<cfquery name="qDeleteFieldFromTable" datasource="MyDSN">
        ALTER TABLE Members
        DROP COLUMN member_visits
</cfquery>

Question? Comments? Email me....