Hi
I had a similar problem awhile back and it was solved here. Now it has
gotten more complex. I have removed uneeded stuff here to keep this
simple.:
I am sure I know why I am getting the results I am, but don't know
how (or if) there is a way around it. If someone can help, I will be
mighty excited and impressed !
Here goes:
Given a table definition:
CREATE TABLE table1
(col1 varchar(20),
col2 varchar(20),
col3 integer,
CONSTRAINT PK_table1 PRIMARY KEY (col1,col2))

and then (assuming table2 exists)

ALTER TABLE table1
   ADD CONSTRAINT FK_table1_table2
   FOREIGN KEY (col1)
   REFERENCES table2 (col1)

REQUIREMENT:
I would like to select some schema information about this table. Among
other things, I want the column name, data type, and Primary Key and
Foreign Key information. For the Primary Key and Foreign Key, all I
need to know is if one or both of these attributes applies to a
column.
Therefore, I would like my result records to look like this:

name  |     type    | PK_col | FK_col
------+----------------+-----------+--------
col1 | varchar    | PK         | FK
col2 | varchar    | PK         |
col3 | integer     |                |

My problem is getting the PK and FK on the same row. My results are
currently like this:

name |     type   | PK_col | FK_col
--------+------------+-----------+--------
col1 | varchar    | PK        |
col1 | varchar    |               | FK
col2 | varchar    | PK        |
col3 | integer     |               |

HERE IS THE QUERY:

SELECT cols.COLUMN_NAME as name,cols.DATA_TYPE as type,
    PK_COL = case when T.CONSTRAINT_TYPE = 'PRIMARY KEY' then 'PK'
    else '' END,
    FK_Col = case when T.CONSTRAINT_TYPE = 'FOREIGN KEY' then 'FK'
    else '' END
FROM Test.INFORMATION_SCHEMA.COLUMNS cols
    left JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
        on cols.table_name = K.TABLE_NAME
        and cols.column_name = K.column_name
left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
   on k.table_name = t.table_name
   and K.CONSTRAINT_NAME = T.CONSTRAINT_NAME
and (T.CONSTRAINT_TYPE = 'PRIMARY KEY'
or T.CONSTRAINT_TYPE = 'FOREIGN KEY' or T.CONSTRAINT_TYPE = 'UNIQUE')
WHERE cols.TABLE_NAME = 'table1'
ORDER BY cols.ORDINAL_POSITION

So, Col1 is duplicated because there are two (2) entries (PK & FK) in
key_column_usage for that column.

Is there some way to combine these two result rows together taking the
PK info from 1 and the FK from the other?

Thanks
Jeff

Hi, Jeff

Did you read my response to your previous post ?
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/c80a0029a2bf3ce9
It's exactly what you are requesting now...

Razvan

Sorry, I had missed your earlier reply because it didn't show up in my
news reader under the question. I see it now. My mistake.

This looks good and makes sense.
Thank you very very much !!
Jeff


Show quoteHide quote

On Fri, 13 Jan 2006 06:38:33 GMT, Jeff User wrote:

(snip)
Show quoteHide quoteHi Jeff,

Just a few simple changes to the query should suffice:

SELECT cols.COLUMN_NAME as name,cols.DATA_TYPE as type,
    PK_COL = MAX(case when T.CONSTRAINT_TYPE = 'PRIMARY KEY' then 'PK'
    else '' END),
    FK_Col = MAX(case when T.CONSTRAINT_TYPE = 'FOREIGN KEY' then 'FK'
    else '' END)
FROM Test.INFORMATION_SCHEMA.COLUMNS cols
    left JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
        on cols.table_name = K.TABLE_NAME
        and cols.column_name = K.column_name
left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
   on k.table_name = t.table_name
   and K.CONSTRAINT_NAME = T.CONSTRAINT_NAME
and (T.CONSTRAINT_TYPE = 'PRIMARY KEY'
or T.CONSTRAINT_TYPE = 'FOREIGN KEY' or T.CONSTRAINT_TYPE = 'UNIQUE')
WHERE cols.TABLE_NAME = 'table1'
ORDER BY cols.ORDINAL_POSITION
GROUP BY cols.COLUMN_NAME, cols.DATA_TYPE, cols.ORDINAL_POSITION

Optimizing an IN clause
SQL 2k5 vs 2k - different results on same execution? ORDER BY prob
What is this called, and how to do it?
SQL Server vs the Competition
Query Problem