| Author |
Topic |
|
natas
Yak Posting Veteran
51 Posts |
 Posted - 2005-06-16 : 11:58:24
|
Hello  , how you all doing?  1. Im using the INFORMATION_SCHEMA stuff.2. I want to figure out which column of a table is the primary key.3. How do i do that?SELECT * FROM information_schema.columns doesnt tell med which one is the primary key..  any help would be much much appreciated.. |
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2005-06-16 : 12:47:30
|
one way using info_schema views:select column_name as 'primary_key'from information_schema.table_constraints pk inner join information_schema.key_column_usage c on c.table_name = pk.table_name and c.constraint_name = pk.constraint_namewhere pk.table_name = <your_table_name> and constraint_type = 'primary key' or using object property:select column_name --, constraint_namefrom information_schema.constraint_column_usagewhere objectproperty(object_id(constraint_name),'isprimarykey') = 1 and table_name = <your_table_name> |
 |
|
|
natas
Yak Posting Veteran
51 Posts |
Posted - 2005-06-16 : 14:45:30
|
| Aah, thanks dude. |
|
|
|
natas
Yak Posting Veteran
51 Posts |
Posted - 2005-06-16 : 17:51:00
|
Hello again.This was really hard to do *pheew*  But this is what i have come up with so far:SELECTINFORMATION_SCHEMA.COLUMNS.TABLE_NAME,INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME,INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION,INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT,INFORMATION_SCHEMA.COLUMNS.IS_NULLABLE,INFORMATION_SCHEMA.COLUMNS.DATA_TYPE,INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH,INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPEFROM INFORMATION_SCHEMA.COLUMNSLEFT OUTER JOIN information_schema.KEY_COLUMN_USAGEONINFORMATION_SCHEMA.COLUMNS.TABLE_NAME=information_schema.KEY_COLUMN_USAGE.TABLE_NAMEAND INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME=information_schema.KEY_COLUMN_USAGE.COLUMN_NAMEAND INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION=information_schema.KEY_COLUMN_USAGE.ORDINAL_POSITIONLEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTSONinformation_schema.KEY_COLUMN_USAGE.TABLE_NAME=information_schema.TABLE_CONSTRAINTS.TABLE_NAMEAND information_schema.KEY_COLUMN_USAGE.CONSTRAINT_NAME=information_schema.TABLE_CONSTRAINTS.CONSTRAINT_NAME--WHERE information_schema.COLUMNS.TABLE_NAME='orders'ORDER BYINFORMATION_schema.COLUMNS.TABLE_NAME,information_schema.COLUMNS.ORDINAL_POSITION Will display this result: TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CONSTRAINT_TYPE-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------ ---------------customers customersID 1 NULL NO int NULL PRIMARY KEYcustomers customername 2 NULL NO varchar 50 NULLorderdetails orderdetailsID 1 NULL NO int NULL PRIMARY KEYorderdetails ordersID 2 NULL YES int NULL NULLorderdetails productsID 3 NULL YES int NULL NULLorders ordersID 1 NULL NO int NULL PRIMARY KEYorders customersID 2 NULL YES int NULL NULLorders ordername 3 NULL NO varchar 50 NULLorders orderdate 4 NULL NO smalldatetime NULL NULLproducts productsID 1 NULL NO int NULL PRIMARY KEYproducts productname 2 NULL NO varchar 50 NULLsysdiagrams name 1 NULL NO nvarchar 128 NULLsysdiagrams principal_id 2 NULL NO int NULL NULLsysdiagrams diagram_id 3 NULL NO int NULL NULLsysdiagrams version 4 NULL YES int NULL NULLsysdiagrams But as you can see in the "CONSTRAINT_TYPE" column, "UNIQUE" AND "FOREIGN KEY" is missing  , only the "PRIMARY KEY" constraint is showing..i want to include every type of constraint ..  some more help would be extremely appreciated |
|
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2005-06-16 : 18:13:58
|
Review this join criteria:AND INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION=information_schema.KEY_COLUMN_USAGE.ORDINAL_POSITION Run this SELECT and youll see what Im getting at:select constraint_name, ordinal_position from information_schema.KEY_COLUMN_USAGE where table_name = 'orders' |
|
|
|
natas
Yak Posting Veteran
51 Posts |
Posted - 2005-06-16 : 18:19:26
|
| hmm so i cant include the ordinal_position?. dont sure what you mean. but i included that one just in case so the SQL-server wont mix up columns with other columns.. |
|
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2005-06-16 : 18:28:37
|
| You are joining on table_name and column_name already, which is unique. You cannot have duplicate column_name within the same table.The ORDINAL_POSITION in the KEY_COLUMN_USAGE table is referring to the column on which the constraint resides.So yea, just remove that from the join |
|
|
|
natas
Yak Posting Veteran
51 Posts |
Posted - 2005-06-16 : 18:39:12
|
| Ahh, I see i removed the "ORDINAL_POSITION" and it worked.. thanks again dude! |
|
|
|
natas
Yak Posting Veteran
51 Posts |
Posted - 2005-06-17 : 07:12:52
|
Hello.. me again  Im now trying to extend my information_schema so it also includes which tablecolumns the FOREIGN KEY references to..SELECTINFORMATION_SCHEMA.COLUMNS.TABLE_NAME,INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME,INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION,INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT,INFORMATION_SCHEMA.COLUMNS.IS_NULLABLE,INFORMATION_SCHEMA.COLUMNS.DATA_TYPE,INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH,INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE,INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME,information_schema.REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_NAME,information_schema.REFERENTIAL_CONSTRAINTS.MATCH_OPTION,information_schema.REFERENTIAL_CONSTRAINTS.UPDATE_RULE,information_schema.REFERENTIAL_CONSTRAINTS.DELETE_RULEFROM INFORMATION_SCHEMA.COLUMNSLEFT OUTER JOIN information_schema.KEY_COLUMN_USAGEONINFORMATION_SCHEMA.COLUMNS.TABLE_NAME=information_schema.KEY_COLUMN_USAGE.TABLE_NAMEAND INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME=information_schema.KEY_COLUMN_USAGE.COLUMN_NAME--AND INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION=information_schema.KEY_COLUMN_USAGE.ORDINAL_POSITIONLEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTSONinformation_schema.KEY_COLUMN_USAGE.TABLE_NAME=information_schema.TABLE_CONSTRAINTS.TABLE_NAMEAND information_schema.KEY_COLUMN_USAGE.CONSTRAINT_NAME=information_schema.TABLE_CONSTRAINTS.CONSTRAINT_NAMELEFT OUTER JOIN information_schema.REFERENTIAL_CONSTRAINTSONinformation_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME=information_schema.KEY_COLUMN_USAGE.CONSTRAINT_NAME--information_schema.REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_NAME=information_schema.KEY_COLUMN_USAGE.CONSTRAINT_NAMEWHERE information_schema.COLUMNS.TABLE_NAME='orders'ORDER BY information_schema.COLUMNS.ORDINAL_POSITION Givs me this output:TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CONSTRAINT_TYPE CONSTRAINT_NAME UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------ --------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------ ----------- -----------orders ordersID 1 NULL NO int NULL PRIMARY KEY PK__orders__7E6CC920 NULL NULL NULL NULLorders customersID 2 NULL YES int NULL FOREIGN KEY FK__orders__customer__7F60ED59 PK__customers__7C8480AE SIMPLE NO ACTION CASCADEorders ordername 3 NULL NO varchar 50 NULL NULL NULL NULL NULL NULLorders orderdate 4 NULL NO smalldatetime NULL NULL NULL NULL NULL NULL NULL I would like to include which table,columns the FOREIGN KEY references to.. but, ah man its tricky like this:TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CONSTRAINT_TYPE CONSTRAINT_NAME UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE FOREIGN_KEY_REFERENCES_TABLE_NAME FOREIGN_KEY_REFERENCES_COLUMN_NAME-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------ --------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------ ----------- ----------- --------------------------------- ----------------------------------orders ordersID 1 NULL NO int NULL PRIMARY KEY PK__orders__7E6CC920 NULL NULL NULL NULL NULL NULLorders customersID 2 NULL YES int NULL FOREIGN KEY FK__orders__customer__7F60ED59 PK__customers__7C8480AE SIMPLE NO ACTION CASCADE NULL NULLorders ordername 3 NULL NO varchar 50 NULL NULL NULL NULL NULL NULL NULL NULLorders orderdate 4 NULL NO smalldatetime NULL NULL NULL NULL NULL NULL NULL NULL NULL |
|
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2005-06-17 : 11:24:29
|
Join again to key_column_refernces and table_constraints with modified join criteria to get the foreign specific info. And introducing, the "Table Alias"  select c.table_name ,c.column_name ,c.ordinal_position ,c.column_default ,c.is_nullable ,c.data_type ,c.character_maximum_length ,tc.constraint_type ,tc.constraint_name ,rc.unique_constraint_name ,rc.match_option ,rc.update_rule ,rc.delete_rule ,tc_fk.table_name 'fk_table' ,kcu_fk.column_name 'fk_column'from information_schema.columns c left outer join information_schema.key_column_usage kcu on c.table_name = kcu.table_name and c.column_name = kcu.column_name left outer join information_schema.table_constraints tc on kcu.table_name = tc.table_name and kcu.constraint_name = tc.constraint_name left outer join information_schema.referential_constraints rc on rc.constraint_name = kcu.constraint_name left join information_schema.key_column_usage kcu_fk on rc.constraint_name = kcu_fk.constraint_name left join information_schema.table_constraints tc_fk on rc.unique_constraint_name = tc_fk.constraint_namewhere c.table_name = 'orders'order by c.ordinal_position |
|
|
|
natas
Yak Posting Veteran
51 Posts |
Posted - 2005-06-17 : 12:36:57
|
| Aahh, perfect , thanks!, your far too kind. |
|
|
|
|
getting the primary key