checking if a table exists?

Is it possible and if so how to check if a table exists? I'm trying to add a automatic setup to my template, user specifies the DSN, and the template will look to see if the tables exist, and if they don't then create them with some basic entries..   I've never created tables in CFM, but I assume CREATE TABLE will work? -mike

> Is it possible and if so how to check if a table exists? Depending on the database but in MSSQL is: if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[que_log]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[que_log] GO AND in MySQL it is; DROP TABLE IF EXISTS que_log; Hope that helps -- Mark Drew http://cybersonic.blogspot.com mailto:mark.drew@gmail.com

----- Excess quoted text cut - see Original Post for more ----- Why not use INFORMATION_SCHEMA.TABLES rather than sysobjects If exists (select * from INFORMATION_SCHEMA.TABLES where table_name = 'YourTable') Much more readable

Well, in CF you would and could make a call to the table name, but place it in a cftry block and getting the result try SELECT * FROM YOURTABLE catch    your code to check if it exists /catch /try BUT  you are best to perform any checking in CF (assuming you are using SQL Server) DECLARE @vcUserTable varchar(500) SET @vcUserTable = 'mychosentablename' IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = @vcUserTable)      BEGIN           SELECT 'TRUE'           -- PRINT 'TRUE' -- < this will be ok      END ELSE      BEGIN           SELECT 'FALSE'           -- PRINT 'FALSE'      END GO Adding this into an SP will be a doddle and you can use it anywhere... HTH N    _____ Sent: 08 June 2004 13:21 To: CF-Talk Subject: checking if a table exists? Is it possible and if so how to check if a table exists? I'm trying to add a automatic setup to my template, user specifies the DSN, and the template will look to see if the tables exist, and if they don't then create them with some basic entries..   I've never created tables in CFM, but I assume CREATE TABLE will work? -mike    _____  

I would also go with your second option, but if the db doesn't allow it and you have to use try/catch don't do "select *". It can possibly return a lot of info. You could use select count(*). In your catch use type="Database" and try to find the correct errorcode for a non-existing table. Pascal ----- Excess quoted text cut - see Original Post for more -----

> It can possibly return a lot of info. If you are doing a select (with a try catch) you can do SELECT * FROM table WHERE 1=2 Hope that helps Mark Drew -- Mark Drew http://cybersonic.blogspot.com mailto:mark.drew@gmail.com

That is a good point Phil.  It is better practice to use INFORMATION_SCHEMA.TABLES as there is no guarantee that the sysobjects table code will always work.... ________________________________________ Sent: 08 June 2004 13:54 To: CF-Talk Subject: RE: checking if a table exists? ----- Excess quoted text cut - see Original Post for more ----- Why not use INFORMATION_SCHEMA.TABLES rather than sysobjects If exists (select * from INFORMATION_SCHEMA.TABLES where table_name = 'YourTable') Much more readable ________________________________________

Will that work in MySql as well as MSSQL?   -Mike ----- Excess quoted text cut - see Original Post for more -----

techmike wrote: > Will that work in MySql as well as MSSQL?   No, MySQL doesn't support schema's. It will work in PostgreSQL though, and I think you can download a SQL script to add information_schema support to Oracle. Jochem

> From: techmike > > Will that work in MySql as well as MSSQL?   No - INFORMATION_SCHEMA is a MSSQL feature

Philip Arnold wrote: > From: techmike >> >> Will that work in MySql as well as MSSQL?   > > No - INFORMATION_SCHEMA is a MSSQL feature The INFORMATION_SCHEMA is not a MS SQL Server specific feature, it comes straight from the SQL standard. MySQL is just not very compliant. Jochem

> From: Jochem van Dieten > > The INFORMATION_SCHEMA is not a MS SQL Server specific feature, > it comes straight from the SQL standard. MySQL is just not very > compliant. Wow, I didn't know this - I've not seen it in all SQL apps, so I thought it was MSSQL Strange that something MS has and others hasn't, yet it's a standard <g>