| Jeff User 2005-12-27, 8:23 pm |
| I 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
|