Getting Index Info

[IForgotAgain]

I know that can use TAG() and KEY() or SYS(14) to get a table's Index TAG and TAG Expression

But I am not sure how to programatically get the Index UNIQUE setting (T/F) and the Index FILTER expression for a given TAG #.

Your advice would be most welcome.

Thanks,
I_Forgot_Again

 

[mspratt]

Try this:

FOR lnTag = 1 TO tagcount()
	m.ctype 	= 'T'  && Index Tag
	m.ctagname 	= UPPER(TAG(lnTag))
	m.ctagexp	= UPPER(SYS(14, lnTag))
	m.lprimary 	= primary(lntag)
	m.lCandidate    = candidate(lntag)
	INSERT INTO dd FROM MEMVAR
ENDFOR

Regards,

Mike

 

[Mike Lewis]

Hi I_Forgot_Again,

Use the CANDIDATE() and PRIMARY() functions.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[IForgotAgain]

Thanks for everyone for their suggestions.

I have not used the PRIMARY() nor the CANDIDATE() functions before.

But I did set up a test data table and built some test indicies.

INDEX Agent_Code + Appl UNIQUE FOR !Sent TAG Test1
INDEX Agent_Code + Appl FOR !Sent TAG Test2
INDEX Agent_Code + Appl TAG Test2

As you can see each of the above indices have different parameters, but ALL of them have the same Expression.

I used my VFP7 interactively via the Command Window to check out the above suggestions.

I got the following:
* TAG gave me the appropriate TAG Names
* SYS(14,n,'alias') and KEY gave me the appropriate Expressions
* CANDIDATE(n,'alias') merely returned .F. for ALL indicies
* PRIMARY(n,'alias') merely returned .F. for ALL indicies

None of the suggested commands help me know if the specific Index Type was UNIQUE (different from the Default (REGULAR))
And none of the suggested commands help me know what was the Index Filter.

How can I get that information programatically?

Your suggestions will be most appreciated.

Thanks,
I_Forgot_Again

 

[IForgotAgain]

Opps....

Pardon my typo's.
The indicies were built correctly, just not written above correctly.

INDEX ON Agent_Code + Appl UNIQUE FOR !Sent TAG Test1
INDEX ON Agent_Code + Appl FOR !Sent TAG Test2
INDEX ON Agent_Code + Appl TAG Test2

Thanks
I_Forgot_Again

 

[ramani]

Hi
I think you are looking for SYS(2021) or its version SYS(2021,indexNumber).



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[IForgotAgain]

Ramani - Thanks SYS(2021, 2, 'alias') was indeed one of those commands that I was looking to find. It successfully gave me the Index Filters for each of my test indicies.

Now what can be used to find the Index Type (Regular/Unique)?

Thanks,
I_Forgot_Again

 

[Mike Lewis]

Hi I_Forget_Again,

* CANDIDATE(n,'alias') merely returned .F. for ALL indicies
* PRIMARY(n,'alias') merely returned .F. for ALL indicies

That's because none of your indexes were primary or candidate. You used the keyword UNIQUE when creating the indexes, but that doesn't give you candidates or primaries -- it gives the old xBase-style unique indexes, which are another matter.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[TamarGranor]

The UNIQUE() function tells you whether a particular tag was created with the unique attribute. However, I'd stay away from UNIQUE indexes if I were you.

Tamar

 

[Olaf Doschke]

Hi Tamar,

Unique may be useful, really depends on the situation. Remember the usage of the INSERT command?

Uses of unique indices: You can easily reduce records to a unique set of records by creating a unique index, DELETE ALL, set order to that index and RECALL ALL. If you want to prevent double values per se a candidate index is of course the better alternative, but needs extra care and/or error handling. With a unique index, inserting records is not prevented, the records with repeated values only won't be indexed. But you may also welcome this behaviour to decide which record should remain later.

All informations about the indices can be retrieved by one function alone: ATAGINFO(). You get: Tag names, index types (primary/candidate/regular/unique), key expressions, filters, (default) orders and collation sequences. All with only one shot...

Bye, Olaf.

 

[Evbcs]

Unique indexes can be a problem if you do not include "FOR NOT DELETED()" in the indexing command.

 

[TamarGranor]

Even with FOR NOT DELETED(), the old kind of unique indexes are a problem because they don't update themselves when a record becomes deleted.

Tamar