Yes, I know about LISTINDEX. What I'd like to figure out is how to find all the tables that have indexing on them (BTREE and/or XReferencing and/or …) and the columns within each table and print them out to a report.

I've recently stumbled across the SYSCOLUMNS table in regards to indexing information. I can do something like:

LIST SYSCOLUMNS TABLE_NAME COLUMN_NAME INDEXED WITH INDEXED # AND WITH INDEXED # 0 From the little bit of checking I've done so far, the information seems to be accurate. What I don't see is any way to really tell what kind of indexing is on a particular column. I do know each column within each table has a flag to indicate that it's indexed or not but I would rather not have to go thru each and every dictionary looking for that information unles I need to. What is the BEST way to get a report on what tables have indexes? Within those table, which columns are indexes and what type of indexing is on that column (BTREE, XRef, Quickdex, …)? What I'd like to be able to do is run the report. Compare what is actually indexed to what should be indexed. We have a couple of sites that run the same application and should be set-up the same. I'd like to be able to quickly determine if the remote sites have everything they need. Plus it can tell us if there may be other indexes that the users need by seeing what addictional indexes have been defined at the various sites. Thank you, Michael Slack </QUOTE> —- === At 27 DEC 1998 04:16AM André wrote: === <QUOTE>I'm not sure what version of AREV you are using but in 3.1, you can simply use the "LISTINDEX" command from the TCL prompt. This gives you a listing of tables that have indexed fields. Once you have a list of the files, you can list each by adding an exclamation point in front of the table name. For example, LIST !CUSTOMERS . Hope this helps. André www.toledoinc.com </QUOTE> —- === At 28 DEC 1998 01:22AM Larry Wilson TARDIS Systems, Inc. wrote: === <QUOTE>Ok, just do these (and don't ask why!) This is all in DICT.SYSCOLUMNS First, make an @ID (it doesn't have one) that has an AMC of 0 (of course) but is part 2, not 0; you'll see why. Make it LEFT 30. Now, make another dict item called INDEXING; this is just for a quick select, it's the next field that does what you want. Indexing should look like A single valued SYMBOLIC that is a BOOLEAN (Yes/No) with the formula looking like: (obviously, this formula, as well as the next, appear all on one line in field 8) @ans=((@id-5,5=_XREF') or @record or @record or @record or @record) Now, make another DICT.SYSCOLUMNS item called INDEX_TYPE. It is a SYMBOLIC, *Multi-valued*, L just in 30 The formula looks like: @ans=' begin case case @id-5,5=_XREF' We have to FIELD the @ID because the first part is the filename Test=FIELD(@id,'*',2); Test-5,5=' @ans=SYMBOLIC CrossRefing ':Test case @record @ans=Btree' case @record; @ans=Crossreferenced by ':@record case @record; Ans=@record @ans=Related to the file ':FIELD(Ans,'*',1):@VM @ans := 'and to the field ':FIELD(ans,'*',2):@VM @ans := 'with a sort A/D,R/L ':FIELD(Ans,'*',3) case @record @ans=Related from ':FIELD(@record,'*',1):@VM @ans := 'and the field ':FIELD(@record,'*',2) end case *** That's it. Now, SELECT SYSCOLUMNS WITH INDEXING BY TABLE_NAME BY (whatever - @ID is the COLUMN_NAME really, it just keeps you from having to do an ID-SUPP; or you could select by AMC) Now, SAVE THAT DARN LIST! I save it away to compare against a current select so that I can see what's changed. Now, GET THAT LIST! LIST SYSCOLUMNS INDEX_TYPE (use this for a single table) LIST SYSCOLUMNS TABLE_NAME COLUMN_NAME INDEX_TYPE ID-SUPP (See, got to suppress the @ID now. Of course, you don't really need to create one like I did, it's just that I list single tables out and compare it with LISTDICT and with an old list). We createding INDEXING because it makes for a MUCH quicker select than anything else. Give it a shot, let me know what you think. Larry Wilson tardis@earthlink.net </QUOTE> —- === At 28 DEC 1998 06:07AM Steve Smith wrote: === <QUOTE>Pretty darned good solution, by anyone's standards! Saves checking the file handles of every opened file from @FILES! Steve </QUOTE> —- === At 28 DEC 1998 08:33AM Larry Wilson TARDIS Systems, Inc. wrote: === <QUOTE>Well, gosh darn, Steve, Thankye! I forgot to mention to him that while he's at it, do a LISTDICT of SYSCOLUMNS and see that INDEXED is BTREE and where RELATING TO is, then write a symbolic that checks to see if the dict item (one of the indexed columns already selected) is right justified; if so, check the CASE SENSITIVE flag to make sure it's 0 since a right justified column is almost always a number. Also, while he's there, he might as well check and make sure that the BTREE (INDEXED in the dict) and RELATED (from or to, I don't remember; just the SOURCE column) all have the MASTER FLAG set. You'd be surprised how many don't. Oh, well, while you're at it, select SYSCOLUMNS with @iDs that don't have the MASTER FLAG set. Oh, yeah, one more thing, check for invalid TABLE_NAMEs or COLUMN NAMES (write symbolics to xlate over for @ID for the valid table name, and xlate over for the COLUMN NAME if xlate(Table_name,Column_name,,'X') else column is bad

Seems a lot of junk gets left somewhere; I found table names that were not anywhere on my system (yes, I checked SYSTABLES; also did a LISTMEDIA of every directory in case it had gotten transfered to another account - several just warn't thar.

SYSCOLUMNS is a LOT more useful that people seem to know. There's even more stuff you can do, but I've run out of energy.

Later,

Larry

tardis@earthlink.net

Thank you very much Mr. Wilson. I've done everything in your first message. It all made sense, no problem there.

I did make a couple of cosmetic changes for INDEXING and INDEX_TYPE symbolics. Instead of using the @ID, I used the {COLUMN_NAME}. I know it isn't considered good programming practice but I wanted it for clarity for the next poor sucker who may have to read them sometime later (probably me after I've forgotten all about it). The other reason to use the COLUMN_NAME within the symbolics is that I'm seriously thinking about removing the @ID column that was built. The reason I'm leaning that way is for the sake of consistancy. It may cause someone a bit of confusion down the line becasue it's different from the norm.

I'm courious about your second message. The only way I can see of puting a BTREE index on the SYSCOLUMNS INDEXED column is to edit the dictionary item for that column and put a "1" in position 6. That is not a problem. I'm mistified by the "RELATING TO" you mentioned. In the little bit of looking around I've been able to do so far, I haven't found any sort of refference to it. I couldn't find it in the DICT.SYSCOLUMNS or in the DICTS listing.

Is there any place that you know of that I can read up on some of the things you mentioned in your second message? I'd like to find out about things like the Master Flag. I've seen it listed on dictionary listings but I don't know what it is supposed to do or how to use it in this case.

As for finding invalid colum names or table names within the SYSCOLUMNS table, once they are identified is there anything speical that needs to be done to get rid of them or can they just be deleted out of the table?

Sorry for pestering you. If you know of some place I can read all about this part of AREV, please let me know then I can look it up myself.

Thank you for all your help.

Michael Slack

Mike,

Until I get my INDEXES FAQ up on my web site, this is way too much to write. Call if you can and I think we can solve the problem pretty quickly. If you have NetMeeting or have WINS server capabilities, so much be better.

Larry Wilson

tardis@earthlink.net

303-680-8555

(MR.? Boy, I'm getting WAY too OLD! No one's called me that since I taught 4th grade - my degrees are in Elem Ed/Child Psych)

View this thread on the forum...