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.
| Author |
Topic |
|
vbud
Starting Member
4 Posts |
 Posted - 2004-04-06 : 03:50:24
|
| I'm writing a stored procedure and for some reasons I need to retrieve the list of primary keys in a given table inside the procedure itself. Anyone knows how can this be achieved? Thanx in advance. |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-04-06 : 04:01:54
|
| Select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTSWHERE CONSTRAINT_TYPE = 'PRIMARY KEY'Damian |
 |
|
|
vbud
Starting Member
4 Posts |
Posted - 2004-04-06 : 05:25:31
|
| Well thanx this is helpful, but one problem though, when I have a table with several primary keys, a composite key consisting of several foreign keys, it would give me only the one denoted as Primary Key in the constraint type. Is there any way to get around this? Thanx in advance. |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-04-06 : 06:03:58
|
| The Constraint_Name returned by Damians query reffers to the constraint for the composite primary key.It is correct.Duane. |
|
|
|
vbud
Starting Member
4 Posts |
Posted - 2004-04-06 : 06:12:18
|
| I got this resolved, thanx anyway. In fact I used something like: declare @TableName varchar(128) select @TableName = 'MyTable' select c.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk , INFORMATION_SCHEMA.KEY_COLUMN_USAGE c where pk.TABLE_NAME = @TableName and CONSTRAINT_TYPE = 'PRIMARY KEY' and c.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAMEThe code is not mine, i found this on the net, now I need to retrieve all the rules that have been defined in the database, INFORMATION_SCHEMA.CHECK_CONSTRAINTS gives me the table constaints onlt but not the defined Rules. Can anyone please, all help appreciated. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-04-07 : 00:11:14
|
| Try sp_help. It displays information on any object in SQL Server. It's a stored procedure though, not a view, so you cannot SELECT from it. You can route it's output into a table though, and SELECT from that.You can also examine the SQL behind sp_help and determine how it gets the information about a rule from the system tables. You can copy that code and modify it to meet your needs. Whatever you do, NEVER try to update or modify the system tables or the data in them. |
|
|
|
|
|
|
|
|
Primary Keys