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 |
|
sujosh
Yak Posting Veteran
55 Posts |
 Posted - 2002-01-29 : 11:44:08
|
| Greetings: In my app I want to get the primarykeys and the datatypes of that column for a table. Now I am aware of sp_pkeys and sp_columns will do it in proably two steps. Is there a one step process where I can get this by passing a table name I can get the pcolumns and pcolum datatypes? Or do you think I have to do it myself?Thanks |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-01-29 : 12:45:05
|
| Check the INFORMATION_SCHEMA views. There's one that list CONSTRAINTS and should have the primary keys listed in it. There's also a COLUMNS view, you can get the datatype from that if the CONSTRAINTS view doesn't have it. Both have a TABLE_NAME column, just set the WHERE clause to query only for the table you need.Check Books Online for more information on INFORMATION_SCHEMA. |
 |
|
|
jbkayne
Posting Yak Master
100 Posts |
Posted - 2002-01-29 : 13:00:58
|
| I was only able to use this method because I use a strict naming convention for constraints/indexes.Enjoy!declare @table_name varchar(50)select @table_name = '???'select col.column_name , col.data_type from information_schema.columns as col inner join information_schema.constraint_column_usage as con on col.column_name = con.column_name and col.table_name = con.table_namewhere col.table_name = @table_name and left(constraint_name,2) = 'PK' |
|
|
|
sujosh
Yak Posting Veteran
55 Posts |
Posted - 2002-01-29 : 13:42:46
|
| Hi Robvolk, Thanks for the pointer. I am going to read on that. It has a lot of info..Jbkayne, Unfortunately the db I am working with does not follow the strcit naming conventions. Thanks for the tip! |
|
|
|
sujosh
Yak Posting Veteran
55 Posts |
Posted - 2002-01-29 : 15:22:34
|
| I found out something from syscolumns. There is a column called typestat. when I read in BOL all it says that it is used for internal use only. But it looks like if has 1 it is a primary key column. But again it is not right always. I came across a table where it had a 1 in the primary key column and a 1 in the next column.Could some one please tell me what is this typestat column denotes?Becasue If I can get the pkey from syscolumns I can do a join to systypes and get the type. Thanks |
|
|
|
|
|
|
Datatypes of Pkeys