I am creating a meta data report for our users TABLEing against SYSTABLE and SYSCOLUM. Unfortunately they do not see non-clustered indices on MS Sequel Server tables of which we have many.

Currently I am using the workaround of editing the MFD's and adding DESCRIPTION='KEY" and filtering on it in my request. But this require manual maintenance of the MFD's. I am also considering creating a description table, but this is also manual maintenance.

Is there some other, automated way to do so?

Thanks

wf does not need to know about any indicies in MS SQL.

To get info on these go to the data dictionaries built in to SQL Server. In this example tables are prefixed with SQLSERVER_.

JOIN CLEAR *
JOIN ID IN sqlserver_sysobjects TO ID IN sqlserver_sysindexes AS J1
JOIN NAME IN SQLSERVER_SYSOBJECTS TO TABLE_NAME IN SQLSERVER_TABLES AS J2
TABLE FILE sqlserver_sysobjects
SUM MAX.ROWCNT BY NAME BY TABLE_SCHEMA BY TABLE_CATALOG NOPRINT
WHERE XTYPE EQ 'U'
WHERE TOTAL MAX.ROWCNT GE &MINRECS
ON TABLE SET LINES 999999
ON TABLE SET STYLE *
TYPE=DATA, COLUMN=NAME, FOCEXEC=ALLMSS(TOD=&TOD.QUOTEDSTRING GOTO=2 CONNECTION=&CONNECTION.QUOTEDSTRING UNIDB=TABLE_CATALOG UNIOWNER=TABLE_SCHEMA UNITABLE=NAME RETRIEVAL=&RETRIEVAL),$

END

Hi,

As mentionned wf does not need to know about sql indexes. Bu at the end wf will send a query to sql server. In that query there are joins selects and so on.
If the concerned tables does not have indexes we should expect performance issues.
If the query sent by webfocus to sql server is not built correctly, this will not trigger sql server to use indexes and it will result in table scanning.
Majid.

I'm with Majid on this one. While WF does not need to know, the programmer probably should. Knowing what indexes are and are not available might lead them to code their program in a such a way to try to take advantage of the indexes that are there and avoid performance issues.

Thanks all.

It took a bit of stumbling around in our MS Sequel Server to find the right locations, but this should give me what I need.

BTW, I am aware that WF doesn't need to know about these indices, the RDBMS does. This is only so I can create meta data listings for our report developers so they can (hopefully) code more efficient Join's and Where's by knowing which columns are indexed.

Gregg, in response to your last statement.
Give your developers the query plan for queries in question, and index the snot out of your tables. It's not a bad thing to have a tremendous amount of indexes, unless you do a lot of DML (insert's, update's, delete's) on the table as all those indexes have to be modified as well.

We're running db2 on an iseries. I use the tool IBM provides to build a tremendous amount of indexes over our more heavily used datawarehouse tables.

Hi, Gregg

I suppose it will be better not TABLEing against WebFOCUS metadata, but MSSQL metadata directly.
E.g.

SQL SQLMSS
SELECT 
    SO.id
    ,Table_name = SO.name
    ,index_name = SI.name
    ,SI.indid 
    ,index_status = SI.status
    ,SIK.keyno
    ,field_name = SC.name
  FROM 
    sysobjects SO
    JOIN sysindexes SI ON SO.id = SI.id
    JOIN sysindexkeys SIK ON SIK.id = SI.id AND SIK.indid = SI.indid
    JOIN syscolumns SC ON SC.id = SIK.id AND SIK.colid = SC.colid    
  WHERE
    SO.xtype = 'U'
    AND SO.id = 2105058535
    AND (SI.status & 8388608) <> 8388608 
    AND (SI.status & 16777216) <> 16777216
  ORDER BY
    SO.id, SI.indid, SIK.keyno
END

If you want more descriptions :
a) look at SELECT * FROM master.dbo.spt_values WHERE type = 'I'
b) look at master.dbo.sp_helpindex and reap what you need.

Regards