Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
checking if a table exists?
Author: Philip Arnold
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:33042#165792
> 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>
Author: Jochem van Dieten
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:33042#165787
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
Author: Philip Arnold
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:33042#165784
> From: techmike
>
> Will that work in MySql as well as MSSQL?
No - INFORMATION_SCHEMA is a MSSQL feature
Author: Jochem van Dieten
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:33042#165782
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
Author: techmike
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:33042#165778
Will that work in MySql as well as MSSQL?
-Mike
----- Excess quoted text cut - see Original Post for more -----
Author: Robertson-Ravo, Neil (RX)
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:33042#165774
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
________________________________________
Author: Mark Drew
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:33042#165767
> 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
Author: Philip Arnold
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:33042#165766
----- 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
Author: Pascal Peters
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:33042#165765
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 -----
Author: Mark Drew
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:33042#165762
> 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
Author: Robertson-Ravo, Neil (RX)
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:33042#165761
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
_____
Author: techmike
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:33042#165759
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
|
May 24, 2012
|
Latest Fusion Authority Articles
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
