query to display all clustered indexes

I took over here a month ago and I am finding that KEY generation is killing
one of my batch processes.
After looking at some of these tables I found that I had a CLUSTERED index
on IPAddress column. I put a stop to that one! With over 600 tables I was
wondering if there is a query to retrieve only the indexes that are
clustered and the column(s) of that index with the table it's associated
with?
That will save me a lot of time.
TIA
Hi
select tbl = object_name(i.id), i.name, c.name,
isunique = indexproperty(i.id, i.name, 'IsUnique'),
isclustered = indexproperty(i.id, i.name, 'IsClustered'),
constrtype = CASE o.type
WHEN 'PK' THEN 'PRIMARY KEY'
WHEN 'UQ' THEN 'UNIQUE'
END
from sysindexes i
join syscolumns c on i.id = c.id
join sysindexkeys k on i.id = k.id
and i.indid = k.indid
and c.colid = k.colid
left join sysobjects o ON o.name = i.name
AND o.xtype in ('PK', 'UQ')
AND o.parent_obj = i.id
where indexproperty(i.id, i.name, 'IsHypothetical') = 0
AND indexproperty(i.id, i.name, 'IsStatistics') = 0
AND indexproperty(i.id, i.name, 'IsAutoStatistics') = 0
order by tbl, i.name, k.keyno
"_Stephen" <srussell@.electracash.com> wrote in message
news:e0XOKLcTGHA.424@.TK2MSFTNGP12.phx.gbl...
>I took over here a month ago and I am finding that KEY generation is
>killing one of my batch processes.
> After looking at some of these tables I found that I had a CLUSTERED index
> on IPAddress column. I put a stop to that one! With over 600 tables I
> was wondering if there is a query to retrieve only the indexes that are
> clustered and the column(s) of that index with the table it's associated
> with?
> That will save me a lot of time.
> TIA
>
>
|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OzSw0TcTGHA.3976@.TK2MSFTNGP10.phx.gbl...
> Hi
> select tbl = object_name(i.id), i.name, c.name,
> isunique = indexproperty(i.id, i.name, 'IsUnique'),
> isclustered = indexproperty(i.id, i.name, 'IsClustered'),
> constrtype = CASE o.type
> WHEN 'PK' THEN 'PRIMARY KEY'
> WHEN 'UQ' THEN 'UNIQUE'
> END
> from sysindexes i
> join syscolumns c on i.id = c.id
> join sysindexkeys k on i.id = k.id
> and i.indid = k.indid
> and c.colid = k.colid
> left join sysobjects o ON o.name = i.name
> AND o.xtype in ('PK', 'UQ')
> AND o.parent_obj = i.id
> where indexproperty(i.id, i.name, 'IsHypothetical') = 0
> AND indexproperty(i.id, i.name, 'IsStatistics') = 0
> AND indexproperty(i.id, i.name, 'IsAutoStatistics') = 0
> order by tbl, i.name, k.keyno
Fantastic!
Thanks again.

query to display all clustered indexes

I took over here a month ago and I am finding that KEY generation is killing
one of my batch processes.
After looking at some of these tables I found that I had a CLUSTERED index
on IPAddress column. I put a stop to that one! With over 600 tables I was
wondering if there is a query to retrieve only the indexes that are
clustered and the column(s) of that index with the table it's associated
with?
That will save me a lot of time.
TIAHi
select tbl = object_name(i.id), i.name, c.name,
isunique = indexproperty(i.id, i.name, 'IsUnique'),
isclustered = indexproperty(i.id, i.name, 'IsClustered'),
constrtype = CASE o.type
WHEN 'PK' THEN 'PRIMARY KEY'
WHEN 'UQ' THEN 'UNIQUE'
END
from sysindexes i
join syscolumns c on i.id = c.id
join sysindexkeys k on i.id = k.id
and i.indid = k.indid
and c.colid = k.colid
left join sysobjects o ON o.name = i.name
AND o.xtype in ('PK', 'UQ')
AND o.parent_obj = i.id
where indexproperty(i.id, i.name, 'IsHypothetical') = 0
AND indexproperty(i.id, i.name, 'IsStatistics') = 0
AND indexproperty(i.id, i.name, 'IsAutoStatistics') = 0
order by tbl, i.name, k.keyno
"_Stephen" <srussell@.electracash.com> wrote in message
news:e0XOKLcTGHA.424@.TK2MSFTNGP12.phx.gbl...
>I took over here a month ago and I am finding that KEY generation is
>killing one of my batch processes.
> After looking at some of these tables I found that I had a CLUSTERED index
> on IPAddress column. I put a stop to that one! With over 600 tables I
> was wondering if there is a query to retrieve only the indexes that are
> clustered and the column(s) of that index with the table it's associated
> with?
> That will save me a lot of time.
> TIA
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OzSw0TcTGHA.3976@.TK2MSFTNGP10.phx.gbl...
> Hi
> select tbl = object_name(i.id), i.name, c.name,
> isunique = indexproperty(i.id, i.name, 'IsUnique'),
> isclustered = indexproperty(i.id, i.name, 'IsClustered'),
> constrtype = CASE o.type
> WHEN 'PK' THEN 'PRIMARY KEY'
> WHEN 'UQ' THEN 'UNIQUE'
> END
> from sysindexes i
> join syscolumns c on i.id = c.id
> join sysindexkeys k on i.id = k.id
> and i.indid = k.indid
> and c.colid = k.colid
> left join sysobjects o ON o.name = i.name
> AND o.xtype in ('PK', 'UQ')
> AND o.parent_obj = i.id
> where indexproperty(i.id, i.name, 'IsHypothetical') = 0
> AND indexproperty(i.id, i.name, 'IsStatistics') = 0
> AND indexproperty(i.id, i.name, 'IsAutoStatistics') = 0
> order by tbl, i.name, k.keyno
Fantastic!
Thanks again.

query to display all clustered indexes

I took over here a month ago and I am finding that KEY generation is killing
one of my batch processes.
After looking at some of these tables I found that I had a CLUSTERED index
on IPAddress column. I put a stop to that one! With over 600 tables I was
wondering if there is a query to retrieve only the indexes that are
clustered and the column(s) of that index with the table it's associated
with?
That will save me a lot of time.
TIAHi
select tbl = object_name(i.id), i.name, c.name,
isunique = indexproperty(i.id, i.name, 'IsUnique'),
isclustered = indexproperty(i.id, i.name, 'IsClustered'),
constrtype = CASE o.type
WHEN 'PK' THEN 'PRIMARY KEY'
WHEN 'UQ' THEN 'UNIQUE'
END
from sysindexes i
join syscolumns c on i.id = c.id
join sysindexkeys k on i.id = k.id
and i.indid = k.indid
and c.colid = k.colid
left join sysobjects o ON o.name = i.name
AND o.xtype in ('PK', 'UQ')
AND o.parent_obj = i.id
where indexproperty(i.id, i.name, 'IsHypothetical') = 0
AND indexproperty(i.id, i.name, 'IsStatistics') = 0
AND indexproperty(i.id, i.name, 'IsAutoStatistics') = 0
order by tbl, i.name, k.keyno
"_Stephen" <srussell@.electracash.com> wrote in message
news:e0XOKLcTGHA.424@.TK2MSFTNGP12.phx.gbl...
>I took over here a month ago and I am finding that KEY generation is
>killing one of my batch processes.
> After looking at some of these tables I found that I had a CLUSTERED index
> on IPAddress column. I put a stop to that one! With over 600 tables I
> was wondering if there is a query to retrieve only the indexes that are
> clustered and the column(s) of that index with the table it's associated
> with?
> That will save me a lot of time.
> TIA
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OzSw0TcTGHA.3976@.TK2MSFTNGP10.phx.gbl...
> Hi
> select tbl = object_name(i.id), i.name, c.name,
> isunique = indexproperty(i.id, i.name, 'IsUnique'),
> isclustered = indexproperty(i.id, i.name, 'IsClustered'),
> constrtype = CASE o.type
> WHEN 'PK' THEN 'PRIMARY KEY'
> WHEN 'UQ' THEN 'UNIQUE'
> END
> from sysindexes i
> join syscolumns c on i.id = c.id
> join sysindexkeys k on i.id = k.id
> and i.indid = k.indid
> and c.colid = k.colid
> left join sysobjects o ON o.name = i.name
> AND o.xtype in ('PK', 'UQ')
> AND o.parent_obj = i.id
> where indexproperty(i.id, i.name, 'IsHypothetical') = 0
> AND indexproperty(i.id, i.name, 'IsStatistics') = 0
> AND indexproperty(i.id, i.name, 'IsAutoStatistics') = 0
> order by tbl, i.name, k.keyno
Fantastic!
Thanks again.