Re: Proper query to create table

This works for me on 15.7:

create table test (test int not null)

declare @i varchar(8000)

select @i = "create index I1 on test (test)"

if not exists (select 1 from sysindexes where id = object_id("test") and name = "I1")
    exec(@i)

Cheers,

Matt.

July 23, 2018 3:02 PM, "Igor Korot" <[email protected]> wrote:

> And following statement also fails:
> 
> IF NOT EXISTS(SELECT o.name, i.name FROM tempdb..sysobjects o,
> tempdb..sysindexes i WHERE o.id = i.id AND o.name='abcattbl' AND
> i.name='abcattbl_tnam_ownr') EXECUTE("CREATE INDEX abcattbl_tnam_ownr
> ON abcattbl(abt_tnam ASC, abt_ownr ASC)")
> 
> "Could not SQLExecute"
> 
> Log says "Index already exists"
> 
> Thank you.
> 
> On Mon, Jul 23, 2018 at 5:57 AM, Igor Korot <[email protected]> wrote:
> 
>> Hi,
>> 
>> On Mon, Jul 23, 2018 at 2:48 AM, Frediano Ziglio <[email protected]> wrote:
>>> 2018-07-22 7:57 GMT+01:00 Igor Korot <[email protected]>:
>> 
>> Hi, ALL,
>> I'm trying to execute following query:
>> 
>> query1 = L"IF NOT EXISTS(SELECT 1 FROM sysobjects WHERE name =
>> 'abcatcol' AND type = 'U') CREATE TABLE abcatcol( <field_list>))";
>> 
>> Unfortunately it fails.
>> If the table does not exist I'm getting 100 (SQL_NO_DATA) from the ODBC
>> driver.
>> And if the table does exist, I'm getting -1 with the error:
>> 
>> "The table already exists".
>> 
>> Is there a way to do this in 1 shot? Or I will have to do a conditional
>> for Sybase to make it in 2 queries?
>> 
>> Please advise.
>> 
>> Thank you.
>>> This works for mssql:
>>> 
>>> if object_id('test123') is null create table test123(i int)
>> 
>> What about this one:
>> 
>> CREATE INDEX IF NOT EXISTS abcattbl_tnam_ownr ON abcattbl(\"abt_tnam\"
>> ASC, \"abt_ownr\" ASC)
>> 
>> ?
>> 
>> It is on Sybase 16.
>> It says "Syntax error on the 'IF'".
>> 
>> Thank you.
>> 
>>> Frediano
>>> _______________________________________________
>>> FreeTDS mailing list
>>> [email protected]
>>> https://lists.ibiblio.org/mailman/listinfo/freetds
> 
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> https://lists.ibiblio.org/mailman/listinfo/freetds