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_SCHEMA.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...
> 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
>--Original Message--
>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_SCHEMA.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...
>> Hello,
>> Does anyone know how to return the names of the
clustered
>> and non clustered indexes from a database table ?
>> Thanks
>> J
>
>.
>|||Hi, Julie
Well , where will we meet?
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:069801c3de95$bb2874f0$a501280a@.phx.gbl...
> Thanks (Again) Uri,
> I think I owe you a couple of drinks ;)
> J
> >--Original Message--
> >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_SCHEMA.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...
> >> Hello,
> >>
> >> Does anyone know how to return the names of the
> clustered
> >> and non clustered indexes from a database table ?
> >>
> >> Thanks
> >> J
> >
> >
> >.
> >|||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...
> 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_SCHEMA.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...
> > Hello,
> >
> > Does anyone know how to return the names of the clustered
> > and non clustered indexes from a database table ?
> >
> > Thanks
> > J
>|||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...
> 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...
> > 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_SCHEMA.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...
> > > Hello,
> > >
> > > Does anyone know how to return the names of the clustered
> > > and non clustered indexes from a database table ?
> > >
> > > Thanks
> > > J
> >
> >
>