Information Schema Query Question

Hello All,
Could someone help provide a query that I can run to
determine the primary key and unique columns for any
given table ?
Thanks in Advance,
AkintoyeHere's an example:
SELECT TC.TABLE_SCHEMA AS TableOwner,
TC.TABLE_NAME,
TC.CONSTRAINT_TYPE,
TC.CONSTRAINT_NAME,
KCU.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
ON TC.TABLE_SCHEMA = KCU.TABLE_SCHEMA
AND TC.TABLE_NAME = KCU.TABLE_NAME
AND TC.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA
AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE IN
(
'PRIMARY KEY',
'UNIQUE'
)
ORDER BY TC.TABLE_SCHEMA, TC.TABLE_NAME, TC.CONSTRAINT_NAME, KCU.COLUMN_NAME
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Akintoye Olorode" <akintoye_olorode@.iwaysoftware.com> wrote in message
news:4366775c$1@.ibixwebf.ibi.com...
> Hello All,
> Could someone help provide a query that I can run to
> determine the primary key and unique columns for any
> given table ?
> Thanks in Advance,
> Akintoye
>|||"Akintoye Olorode" <akintoye_olorode@.iwaysoftware.com> wrote in
news:4366775c$1@.ibixwebf.ibi.com:

> Could someone help provide a query that I can run to
> determine the primary key and unique columns for any
> given table ?
You're looking for simething like this? (SQL Server 2005):
select ccu.column_name from
information_schema.constraint_column_usage ccu inner join
information_schema.table_constraints tc
on (tc.constraint_name = ccu.constraint_name)
where
(tc.constraint_type in ('unique','primary key')) and
(tc.table_schema + '.' + tc.table_name = <schema>.<table_name> )
I guess that would be approximately like this in SQL Server 2000 (Not
tested):
select ccu.column_name from
information_schema.constraint_column_usage ccu inner join
information_schema.table_constraints tc
on (tc.constraint_name = ccu.constraint_name)
where
(tc.constraint_type in ('unique','primary key')) and
(tc.table_name = <table_name> )
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in
news:Xns9700F370E9A4Dolekristianbangaas@.
207.46.248.16:
(...)
Ooops, stayed a littlebit too long in my outbox :( Sorry folks.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging

Information Schema Query Question

Hello All,

Could someone help provide a query that I can run to
determine the primary key and unique columns for any
given table ?

Thanks in Advance,

Akintoyeselect t.table_name, t.constraint_type, t.constraint_name,
c.column_name
from information_schema.TABLE_CONSTRAINTS t
join information_schema.CONSTRAINT_COLUMN_USAGE c ON t.constraint_name
= c.constraint_name
where constraint_type IN ('UNIQUE', 'PRIMARY KEY')
and t.table_name = <Table Name
Akintoye Olorode wrote:
> Hello All,
> Could someone help provide a query that I can run to
> determine the primary key and unique columns for any
> given table ?
> Thanks in Advance,
> Akintoyesql