check if the table is already existing

[Merlyn MM]

I need to check if the table is already existing in the database before
creating a new table dynamically. If the table is already there then I have to
display the table columns and the datatypes. Can anyone help me how can i do
this?

[mxstu]

Another option would be to use CFPARAM to ensure that the form field values
exist.

Since FORM is a structure, you can also use this syntax instead of evaluate:
#form["Tour"& ThisRow]#

I noticed the single quotes around most of the values in the query. Are
"NumberAttending", "Tour", "Mixer", etc... "text" columns? If so, you might
want to use column data types that more accurately reflects the kind of values
contained in each column (in this case numeric and yes/no). For example, if
"NumberAttending" should only contain numbers, then the column type should
really be numeric not text. You probably wouldn't want someone entering "I am
bringing my entire extended family!" in the "NumberAttending" column : )

[mxstu]

There are probably a few methods for accommplishing this, but the specifics
depend on the type of database you are using. For example, SQL Server provides
"views" of system information like table objects and table columns, which can
be used to determine if a table exists or to retrieve information about table
columns.

-- should be modified to filter on database and table owner
IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =
'YourTable')
BEGIN
-- some code here...
END
ELSE
BEGIN
SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTable'
END

For security and other reasons, you may want to establish a special user/login
in the database and grant it limited permissions for working with the dynamic
objects. There are probably a number of both pros and cons to allowing an
interface to dynamically create database objects, so you may want to
investigate that aspect further.


HTH