Combination of composite Primary Key [Solved]

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Combination of composite Primary Key [Solved]

Author

Topic

alex100
Starting Member

5 Posts

Posted - 2008-10-08 : 12:30:53

hello,

I've created a table(myTable) on Sql Server.
This table has a composite Primary Key(this PK is a combination of several columns).

I would like to see/know using sql statement, what is the combination of composite Primary Key for that table.

I know how to get the Primary Key, but don't khow how to drill down in oder to get columns names.

This is the sql for Primary Key:

SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS CONST
WHERE (CONST.CONSTRAINT_TYPE = 'PRIMARY KEY') AND
(CONST.TABLE_NAME = 'myTable')

thanks for the help,
Alex

how to know the combination of composite primary key & sql server

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 12:58:17

you just need to right click the table and script it out from sql management studio. you will get full script including the creation of primary key which will involve all the columns details

alex100
Starting Member

5 Posts

Posted - 2008-10-08 : 13:38:31

This is not a solution for my issue, but thanks for the answer.
In my case i have a table (even several tables) ,which i didn't create.
Like i said before , i need an SQL statement so i could dynamicly(without management studio) knowing only table name, get columns names for composite Primary Key.

thanks for the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 13:46:54

do you meant this?

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS CONST 
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS COL
ON COL.CONSTRAINT_NAME=CONST.CONSTRAINT_NAME 
WHERE (CONST.CONSTRAINT_TYPE = 'PRIMARY KEY') AND 
(CONST.TABLE_NAME = 'myTable')

ddamico
Yak Posting Veteran

76 Posts

Posted - 2008-10-08 : 14:07:01

This should simplify the last reply if you just want the columns

COLUMN_NAME is what you want

SELECT TC.TABLE_NAME
, TC.CONSTRAINT_NAME
, CCU.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
LEFT OUTER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
ON TC.[CONSTRAINT_NAME] = CCU.[CONSTRAINT_NAME]
WHERE (TC.CONSTRAINT_TYPE = 'PRIMARY KEY') AND
(TC.TABLE_NAME = 'TableName')

alex100
Starting Member

5 Posts

Posted - 2008-10-09 : 00:05:26

Thanks visakh16, this is exactly the solution that i needed.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-09 : 01:03:14

quote:
Originally posted by alex100

Thanks visakh16, this is exactly the solution that i needed.

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources