Re: Get index information from information_schema?

On Mar 18, 2008, at 5:42 PM, Dann Corbit wrote:

>> correct results for this query:
>
> create table t1 (c1 int not null, c2 int not null, c3 char(5), c4 int,
> c5 int, constraint pk_t1 PRIMARY KEY (c5,c4));
>
> create table t2 (tc1 int not null, c1 int not null, c2 int not null,
> c5
> char(5), constraint fk_t2 FOREIGN KEY (c1,c2) references t1 (c5,c4));
>
> select CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION from
> information_schema.key_column_usage where TABLE_NAME in ('t1', 't2');
>
> So the only thing that will be missing are the indexes that are none
> of
> the above.
>
> It's a shame that there really is no information schema for indexes in
> that category.

Careful with that, foreign keys are not at all indexes although it is
common practice to put indexes on columns with them.

Erik Jones

DBA | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com