| Author |
 Topic  |
|
Da_Stimulator
DEV Team Forum Moderator
USA
3373 Posts |
 Posted - 30 December 2004 : 17:21:49
|
For a setup script I'm working on, I need to check and see if a table exists in the database before creating that table... is there a way I can check this without producing an error?
I saw an article on 4Guys about something like:
if EXISTS (SELECT * FROM INFORMATION_SCHEMA.tables WHERE table_name='MyTable')
select * from MyTable
but that wasnt too informative to me... I'm working with sql server from asp point of view, and I dont know how to translate that into a query... |
-Stim |
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
|
|
Da_Stimulator
DEV Team Forum Moderator
USA
3373 Posts |
Posted - 30 December 2004 : 17:31:17
|
| not much, and the application I'm writing isnt big enough to warrant a learning experience I dont think.... unless there's no other way |
-Stim |
 |
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 30 December 2004 : 17:43:16
|
You should be able to use that directly as a query with SQL Server:
strSQL = "IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.tables WHERE table_name='[name of your table here]') SELECT * from [same table name here]"
This will check if that table exists and, if so, it will pull all the records from it. Change the last part to do what you want like:
strSQL = "IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.tables WHERE table_name='[name of your table here]') CREATE TABLE Person ( LastName varchar, FirstName varchar, Address varchar, Age int )"
that help any?
|
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
|
|
Da_Stimulator
DEV Team Forum Moderator
USA
3373 Posts |
Posted - 30 December 2004 : 17:45:16
|
| thx alot D3mon |
-Stim |
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
|
|
Da_Stimulator
DEV Team Forum Moderator
USA
3373 Posts |
Posted - 30 December 2004 : 22:55:00
|
Didnt work D3mon :( I dunno why, it never bothered creating the tables...
strsql = "IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.tables WHERE table_name='KPOLL_CONFIG') "
strsql1 = "CREATE TABLE KPOLL_CONFIG (" & _
"CONFIG_ID INT IDENTITY NOT NULL, " & _
"CONFIG_VARIABLE VARCHAR(100) NOT NULL, " & _
"CONFIG_VALUE VARCHAR(100) NOT NULL" & _
")"
pConn.execute(strsql & strsql1)
strsql = "IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.tables WHERE table_name='KPOLL') "
strsql1 = "CREATE TABLE KPOLL (" & _
"POLL_ID INT IDENTITY NOT NULL, " & _
"POLL_TITLE VARCHAR(100) NOT NULL, " & _
"POLL_ACTIVE INT NOT NULL DEFAULT 0" & _
")"
pConn.execute(strsql & strsql1)
strsql = "IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.tables WHERE table_name='KPOLL_OPTIONS') "
strsql1 = "CREATE TABLE KPOLL_OPTIONS ("
strsql1 = strsql1 & "OPTION_ID INT IDENTITY NOT NULL, " & _
"POLL_ID INT NOT NULL, " & _
"OPTION_VALUE VARCHAR(100) NOT NULL, " & _
"OPTION_VOTES INT NOT NULL DEFAULT 0" & _
")"
pConn.execute(strsql & strsql1)
|
-Stim |
|
|
|
Da_Stimulator
DEV Team Forum Moderator
USA
3373 Posts |
Posted - 30 December 2004 : 23:01:01
|
aha, got it working, works great, thx!!
was a bug in the function I had to check whether or not to get that far in the code to create the tables :P
Now... is there any way to load whether or not a table exists into an asp variable boolean? |
-Stim |
Edited by - Da_Stimulator on 30 December 2004 23:11:38 |
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 31 December 2004 : 06:31:15
|
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.tables WHERE table_name='KPOLL')
SELECT 1 ELSE SELECT 0
should work returning a 1 for 'exists' and 0 for 'doesnt'
then,
doesTableExist = objRS(0) |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
Edited by - D3mon on 31 December 2004 06:32:37 |
|
|
|
Da_Stimulator
DEV Team Forum Moderator
USA
3373 Posts |
Posted - 31 December 2004 : 07:04:33
|
| so doesTableExist = objRS(0) will return a 0 or 1 value (false or true as boolean)? |
-Stim |
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 31 December 2004 : 10:14:07
|
if the table exists then you'll get a 1 back in your recordset, otherwise 0 (zero). ASP should be able to recognise this as True(1) and False(0) quite easily. If you still have problems, get SQL to return a string instead like:
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.tables WHERE table_name='KPOLL')
SELECT 'True' ELSE SELECT 'False' |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
|
|
|
Da_Stimulator
DEV Team Forum Moderator
USA
3373 Posts |
Posted - 05 January 2005 : 06:00:48
|
Ok, thanks alot for your help so far D3mon 
Now some more inquiries... now that I have that down and working to my liking.
Can I combine statements like that to produce multiple records and/or an array of data?
For example, could I execute something like this, then retrieve the true/false bit for each table somehow?
IF EXISTS
(
SELECT * FROM INFORMATION_SCHEMA.tables WHERE table_name='TABLE1'
OR table_name='TABLE2'
OR table_name='TABLE3'
)
SELECT 1 ELSE SELECT 0
Also, could this type of code be used to check if a stored procedure exists or not? if so how? |
-Stim |
Edited by - Da_Stimulator on 05 January 2005 06:06:58 |
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
|
|
Da_Stimulator
DEV Team Forum Moderator
USA
3373 Posts |
Posted - 07 January 2005 : 01:35:00
|
D3mon, I'm currently playign around with stuff from that link you posted, but I had a user today send me an email, containing error code saying there is a permissions problem accessing the INFORMATION_SCHEMA table.
Microsoft OLE DB Provider for ODBC Drivers error '80040e09'
[Microsoft][ODBC SQL Server Driver][SQL Server]SELECT permission denied on object 'TABLES', database 'master', owner 'INFORMATION_SCHEMA'.
/blah/blah/kpoll_config.asp, line 49
The sql string being queried is
strsql = "IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.tables WHERE table_name='KPOLL') "
strsql = strsql & "SELECT 1 ELSE SELECT 0"
set rs = pConn.execute(strsql)
I have a feeling its permissions in the database somehow, but hopefully I can find a work around. I fear the sysobjects table probably holds the same restrictions. |
-Stim |
|
|
|
Topic |
|
Topic Locked
