Returning Primary Keys and indexes ?

Hello,
Does anyone know how to return the names of the clustered
and non clustered indexes from a database table ?
Thanks
JJulie
SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA as TableOwner,
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME,
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME,
case
objectproperty(object_id(INFORMATION_SCH
EMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM
E)
,'CnstIsClustKey')
when 1 then 'clustered'
when 0 then 'noneclustered'
end as IsClustered
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME =
INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
WHERE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE LIKE 'PRIMARY
KEY'
ORDER BY INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:059e01c3de7b$17a85f80$a401280a@.phx.gbl...

quote:

---

> Hello,
> Does anyone know how to return the names of the clustered
> and non clustered indexes from a database table ?
> Thanks
> J

---

|||Thanks (Again) Uri,
I think I owe you a couple of drinks ;)
J

quote:

---

>--Original Message--
>Julie
>SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA

---

as TableOwner,

quote:

---

> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME,
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME,
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME,
> case
>objectproperty(object_id

---

(INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM

quote:

---

>E)
>,'CnstIsClustKey')
> when 1 then 'clustered'
> when 0 then 'noneclustered'
> end as IsClustered
>FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN
>INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME =
>INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
>WHERE

---

INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE
LIKE 'PRIMARY

quote:

---

>KEY'
>ORDER BY INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME
>"Julie" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:059e01c3de7b$17a85f80$a401280a@.phx.gbl...
clustered[QUOTE]
>
>.
>

---

|||Hi, Julie
Well , where will we meet?
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:069801c3de95$bb2874f0$a501280a@.phx.gbl...[QUOTE]
> Thanks (Again) Uri,
> I think I owe you a couple of drinks ;)
> J
>
> as TableOwner,
> (INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE
> LIKE 'PRIMARY
> message
> clustered|||I am not really sure but the provided select only returns
constraint related indexes.
Indexes which are only present for performance are not
returned.
ben brugman
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u6T2tFo3DHA.2544@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Julie
> SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA as TableOwner,
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME,
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME,
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME,
> case
>

---

objectproperty(object_id(INFORMATION_SCH
EMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM

quote:

---

> E)
> ,'CnstIsClustKey')
> when 1 then 'clustered'
> when 0 then 'noneclustered'
> end as IsClustered
> FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME =
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
> WHERE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE LIKE 'PRIMARY
> KEY'
> ORDER BY INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME
> "Julie" <anonymous@.discussions.microsoft.com> wrote in message
> news:059e01c3de7b$17a85f80$a401280a@.phx.gbl...
>

---

|||ben
declare @.sql varchar(8000)
set @.sql = ''
select @.sql = @.sql + case when exists (
select * from information_schema.constraint_table_usage
where constraint_name = sysindexes.name)
then tablename+'.'+ name
else tablename + '.' + name end + char(10)
from (select top 100 percent object_name(id)tablename, name
from sysindexes
where objectproperty(id, 'IsMSShipped') = 0
and indid between 1 and 254
and (status & 64) = 0
order by tablename, indid desc) sysindexes
print @.sql
"ben brugman" <ben@.niethier.nl> wrote in message
news:ej6LMXN4DHA.2528@.TK2MSFTNGP09.phx.gbl...

quote:

---

> I am not really sure but the provided select only returns
> constraint related indexes.
> Indexes which are only present for performance are not
> returned.
> ben brugman
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:u6T2tFo3DHA.2544@.TK2MSFTNGP10.phx.gbl...
>

---

objectproperty(object_id(INFORMATION_SCH
EMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM

quote:

---

>

---