Re: SMI query for Index column names
sanjeev sagar wrote:
>
> Hey Guy's,
>
> IDS 7.30 UC2
> O/S-Solaris 2.5.1
>
> I need to find out the columns of all the indexes on
> a table. I tried this
>
> select c.colname from systables t, sysindexes x,> syscolumns c where t.tabid=x.tabid and
> x.tabid=c.tabid and t.tabname='items' and
> c.colno=x.part1
>
> This gives me the head of the indexes. I know that
> sysindexes keep all the colno in part1-part16. I am
> not able to fig out that how to use part1-part16 in
> the query to find out all the columns in all the
> indexes.
>
If you need that informations for executing UPDATE STATISTICS
this SHELL script will help:
#-----------------------------------------------------------------------
a="2 3 4 5 6 7 8 9 10 11 12 13"
for i in $a; do
echo $i
dbaccess dbname - << EOF
unload to low.sql
select "update statistics low for table ",
t.tabname,"(",c.colname,");"
from systables t,syscolumns c,sysindexes i
where t.tabtype = 'T' and
c.tabid=t.tabid and t.tabid=i.tabid and i.part$i=c.colno;EOF
if [ -s low.sql ]; then
sed 's/|/ /g' low.sql > low2.sql
dbaccess dbname low2.sql
else
echo "No more index"
break
fi
done
#------------------------------------------------------------------------
Best regards,