Information schema and Primary Keys

user_665557 0
Viewed 17335

How do I just print out a 'primary key' for the column with the primary key?

I get 'primary key' for all the columns if the table has a primary key, instead of the one column with the primary key and the other columns as blank in keyType.

   SELECT c.TABLE_NAME, 
          c.COLUMN_NAME, 
          c.DATA_TYPE, 
          c.Column_default, 
          c.character_maximum_length, 
          c.numeric_precision, 
          c.is_nullable,
          CASE 
            WHEN u.CONSTRAINT_TYPE = 'PRIMARY KEY' THEN 'primary key'
            ELSE '' 
          END AS KeyType
     FROM INFORMATION_SCHEMA.COLUMNS as c
LEFT JOIN information_schema.table_constraints as u ON c.table_name = u.table_name
 ORDER BY table_name
information-schema primary-key sql-server-2005 constraints sql-server
4 Answers

I've found this code below to be the simplest method to find the primary key for a table. It only requires a left outer join between the information_schema.columns table and the information_schema.key_column_usage table.

Select 
  col.column_name, 
  col.data_type, 
  col.ordinal_position, 
  col.is_nullable, 
  case when ky.COLUMN_NAME is null then 0 else 1 end as primary_key_flag
from <your-catalog>.Information_Schema.columns col
  left outer join <your-catalog>.INFORMATION_SCHEMA.KEY_COLUMN_USAGE ky
    on ky.COLUMN_NAME = col.COLUMN_NAME and ky.TABLE_NAME = col.TABLE_NAME 
      and ky.TABLE_CATALOG = col.TABLE_CATALOG and ky.table_name = col.table_name
where 
  col.TABLE_NAME = '<your-table-name>' 
  and col.table_catalog = '<your-catalog>';

Note: This code was built using SQL Server 2017 Express (14.0.1000) and after I created the primary key, I was able to see those values in the KEY_COLUMN_USAGE table. I also saw a record in the CONSTRAINT_TABLE_USAGE which identified the constraint_name, but I didn't find that to be a requirement in order to get the join into which revealed the primary key.

Final note: I'm just getting back into SQL server on a personal project after nearly a decade away from the DB (using MySQL), and so I'm nowhere near an expert on the subject.

user_33969620
Related