'Creating index if index doesn't exist

I have a problem creating an index with the advantage database server if it doesn't exist with a sql query.

My query looks like this:

 If not Exists(<SELECT Query for amount of indizes for one column>) then 
 Create Index Test on Tablename (No); endif

So I don't use FullTextSearchIndizes,because it is a integer field. Otherwhise it would look like this:

 If not Exists(SELECT * FROM tablename WHERE CONTAINS( * , 'Test' )) then 
 Create Index Test on Tablename (Name) Content; endif

So, my only problem is how do I get the indices. I've read in other DBMS you can use sys.indexes and some other things.

sqlindexingadvantage-database-server


Solution 1:[1]

There is a simple approach to do that:

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_MyTable_MyColumn') 
    CREATE INDEX [IX_MyTable_MyColumn] ON [dbo].[MyTable] ([MyColumn]);

Solution 2:[2]

Try something more like this, utilizing the system commands. This is a working example I use on an Advantage Database:

IF (SELECT Name FROM system.indexes
   WHERE Index_File_Name = 'GLDept.adi'
   AND Index_Expression = 'DeptNumber') IS NULL
THEN
   EXECUTE PROCEDURE sp_CreateIndex90( 
   'GLDept',
   'GLDept.adi',
   'DEPTNUMBER',
   'DeptNumber',
   '',
   2051,
   512,
   '' );
END IF;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 ADM-IT
Solution 2 Daniel

Related Questions