SCHEMA - primary key - SQL problem

[Jeff User]

Hi
I posted this in another group and got no reply.
Hope someone here can help. Perhaps this is more of a SQL programming
issue than a schema or PK issue, as the actual problem is duplicate
records:

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_LENGTH is null
then st.length
else CHARACTER_MAXIMUM_LENGTH END,
CHARACTER_MAXIMUM_LENGTH,
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...
(where PK_Tester is the name of the Primary Key constraint)
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...
(where PK_Tester is the name of the Primary Key constraint
and FK_Tester is the name of the Fioreign Key constraint)

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 idea what?

Thanks in advance
Jeff

[Trey Walpole]

so you're trying to get the column list and indicate which column(s) are
int the PK and FKs?

change your FROM and WHERE to be

the dupes and repeating PKs are fixed by these changes
* have to move the constraint_type from the where to the left join
[otherwise, it will act like an inner join]
* have to join the key_column_usage view on the column as well
[otherwise, you get all columns showing as the PK]

FROM (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

and k.column_name = cols.column_name

and T.CONSTRAINT_TYPE = 'PRIMARY KEY'

WHERE cols.TABLE_NAME = 'Tester'

[Jeff User]

Thanks again
Works great !!
Jeff

[Razvan Socol]

Hi, Jeff

First, we must consider that:
1. A primary key (or a foreign key) may consist of more than one
column.
2. A column may be part of the primary key AND a foreign key.
3. A column may be part of more than one foreign key.
4. Unique keys have almost the same importance as the primary key.

In case (2) or (3) happens, we need to define if:
a) all the PK/FK names are displayed (therefore causing duplicates in
the column names)
b) only one PK/FK name is displayed for each column (the PK or the
first of the FK-s).

In the mean time, I would use the following query (which doesn't
display any PK/FK name), to see if each column is part of the PK or is
part of a FK:

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION, NUMERIC_SCALE,
CASE WHEN EXISTS (
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
ON kcu.CONSTRAINT_NAME=tc.CONSTRAINT_NAME
AND kcu.CONSTRAINT_SCHEMA=tc.CONSTRAINT_SCHEMA
WHERE tc.TABLE_NAME=cols.TABLE_NAME
AND tc.TABLE_SCHEMA=cols.TABLE_SCHEMA
AND kcu.COLUMN_NAME=cols.COLUMN_NAME
AND tc.CONSTRAINT_TYPE='PRIMARY KEY'
) THEN 'PK' ELSE '' END AS PK,
CASE WHEN EXISTS (
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
ON kcu.CONSTRAINT_NAME=tc.CONSTRAINT_NAME
AND kcu.CONSTRAINT_SCHEMA=tc.CONSTRAINT_SCHEMA
WHERE tc.TABLE_NAME=cols.TABLE_NAME
AND tc.TABLE_SCHEMA=cols.TABLE_SCHEMA
AND kcu.COLUMN_NAME=cols.COLUMN_NAME
AND tc.CONSTRAINT_TYPE='FOREIGN KEY'
) THEN 'FK' ELSE '' END AS FK
FROM INFORMATION_SCHEMA.COLUMNS cols
WHERE TABLE_NAME='YourTableName'
ORDER BY ORDINAL_POSITION

Also, it is not clear to me what is the length/size for each column
that you want to be displayed. If it's CHARACTER_MAXIMUM_LENGTH, then
it won't be the actual size (in bytes), because nvarchar/nchar are
stored using 2 bytes/character.

Razvan