RE: checking if a table exists?
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 BUTyou 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 _ From: techmike [mailto:[EMAIL PROTECTED] 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 _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: checking if a table exists?
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:[EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: checking if a table exists?
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 -Original Message- From: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Sent: dinsdag 8 juni 2004 14:27 To: CF-Talk Subject: RE: checking if a table exists? 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 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: checking if a table exists?
From: Mark Drew 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 Why not use INFORMATION_SCHEMA.TABLES rather than sysobjects If exists (select * from INFORMATION_SCHEMA.TABLES where table_name = 'YourTable') Much more readable [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: checking if a table exists?
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:[EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: checking if a table exists?
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 From: Philip Arnold [mailto:[EMAIL PROTECTED] Sent: 08 June 2004 13:54 To: CF-Talk Subject: RE: checking if a table exists? From: Mark Drew 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 Why not use INFORMATION_SCHEMA.TABLES rather than sysobjects If exists (select * from INFORMATION_SCHEMA.TABLES where table_name = 'YourTable') Much more readable [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: checking if a table exists?
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 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: checking if a table exists?
From: techmike Will that work in MySql as well as MSSQL? No - INFORMATION_SCHEMA is a MSSQL feature [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: checking if a table exists?
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 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: checking if a table exists?
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 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
