Drop Table
Support Forum for database administrators and web based access to important newsgroups related to databasesI have reviewed previous posts about finding primary key, table schema information, etc... I have got quite far but am now getting duplicate records. Not sure what else to add here to fix this. If I use this query, I get various column information and the Primary key is identified by my PK_Col column correctly: SELECT cols.COLUMN_NAME, DATA_TYPE, Length = case when CHARACTER_MAXIMUM_LE NGTH is null then st.length else CHARACTER_MAXIMUM_LE NGTH END, CHARACTER_MAXIMUM_LE NGTH, PK_COL = case when K.COLUMN_NAME = cols.COLUMN_NAME then 'PK' else '' END, T.CONSTRAINT_NAME, IS_NULLABLE FROM (Test.INFORMATION_SCHEMA.COLUMNS cols left join systypes st on cols.DATA_TYPE = st.name) left join (INFORMATION_SCHEMA.TABLE_CONSTRAINTS T INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K ON T.CONSTRAINT_NAME = K.CONSTRAINT_NAME) on T.TABLE_NAME = cols.TABLE_NAME WHERE cols.TABLE_NAME = 'Tester' and T.CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY cols.COLUMN_NAME The table "tester" has one PK (first column) and one foreign key (2nd column). The above query returns one row for each column in table tester. So this is correct results: COL_1 varchar 26 26 PK PK_Tester NO COL_2 Int 4 4 PK_Tester NO etc... Now I also would like to see if a column is a foreign key. However, when I change the WHERE clause like this : WHERE cols.TABLE_NAME = 'Tester' and (T.CONSTRAINT_TYPE = 'PRIMARY KEY' or T.CONSTRAINT_TYPE = 'FOREIGN KEY') I now get 2 records for every table column represented. The name of the PK and the name of the FK are each displayed for every table column row of data I get back; For expl: This is what I should get: COL_1 varchar 26 26 PK PK_Tester NO COL_2 Int 4 4 PK FK_Tester NO etc... But this is what I am getting: COL_1 varchar 26 26 PK PK_Tester NO COL_1 varchar 26 26 FK_Tester NO COL_2 Int 4 4 PK FK_Tester NO COL_2 Int 4 4 PK_Tester NO Note, I didnt set up code to display FK yet, in the PK_Col column. That will come after I resolve the duplicate result rows problem. Do I need to add more criteria to a JOIN clause or a WHERE clause and if so, any aidea what? Thanks Jeff
Post Follow-up to this message
Show a Printable Version
Email This Page to Someone!
Receive updates to this thread