Retrieve Primary key coloumn by querying system table of Sql server 2000

Hi,
How can i retrieve primary key columns including foreign key ( part of
primary key) by querying system tables of sql server 2000. I want to
know that how many columns are primary key column in table.
Pls help.
Thanks in advance
ShailSee if this helps:
--In the WHERE clause change the table name and table owner to your table
name and owner.
SELECT TC.TABLE_SCHEMA,
TC.TABLE_NAME,
TC.CONSTRAINT_TYPE,
TC.CONSTRAINT_NAME,
KCU.COLUMN_NAME,
KCU.ORDINAL_POSITION
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_NAME = KCU.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND TC.TABLE_SCHEMA = 'dbo'
AND TC.TABLE_NAME = 'txsh0hor'
ORDER BY KCU.ORDINAL_POSITION
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<shailbpl@.gmail.com> wrote in message
news:1150279652.629125.32380@.u72g2000cwu.googlegroups.com...
Hi,
How can i retrieve primary key columns including foreign key ( part of
primary key) by querying system tables of sql server 2000. I want to
know that how many columns are primary key column in table.
Pls help.
Thanks in advance
Shail|||Here's an example of getting the columns of a primary key constraint:
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'author
s'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<shailbpl@.gmail.com> wrote in message news:1150279652.629125.32380@.u72g2000cwu.googlegroups
.com...
> Hi,
> How can i retrieve primary key columns including foreign key ( part of
> primary key) by querying system tables of sql server 2000. I want to
> know that how many columns are primary key column in table.
> Pls help.
> Thanks in advance
> Shail
>|||sp_help 'tableName'
OR
select * from information_schema.table_constraints
where constraInt_type IN ('PRIMARY KEY','FOREIGN KEY')
Madhivanan
shailbpl@.gmail.com wrote:
> Hi,
> How can i retrieve primary key columns including foreign key ( part of
> primary key) by querying system tables of sql server 2000. I want to
> know that how many columns are primary key column in table.
> Pls help.
> Thanks in advance
> Shail|||See whether this helps
SELECT a.pktable,A.fktable,a.fkcol,a.[name]
FROM
(SELECT a.*,b.[name]
FROM
(SELECT object_name(fkeyid) as FkTable ,
object_name(rkeyid) as PkTable,
b.name as fkCol ,rkey,rkeyid
FROM sysforeignkeys a
INNER JOIN syscolumns b
ON a.fkey=b.colid and a.fkeyid=b.id ) a
LEFT OUTER JOIN syscolumns b
ON a.rkey=b.colid where a.rkeyid=b.id ) a
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
<shailbpl@.gmail.com> wrote in message
news:1150279652.629125.32380@.u72g2000cwu.googlegroups.com...
> Hi,
> How can i retrieve primary key columns including foreign key ( part of
> primary key) by querying system tables of sql server 2000. I want to
> know that how many columns are primary key column in table.
> Pls help.
> Thanks in advance
> Shail
>