info about identity and primary key

• aakcse

  Valued Member

  Points: 54

  More actions

  February 20, 2009 at 1:04 am

  #131085

  Hi All,

  Can any of you help me in getting info about, weather the col is primay key and weather the col is identity.

  I have to insert these info into my own data dict table

  using information schema i got most of the info except primay key

  and identity property of the col.

  insert into mydict

  values

  select column_name,

  table_name,

  data_type,

  --primay ( 1 if primary else 0 if not primary)

  --idenity( 1 if identiy else 0 if not identity)

  ordinal_position

  from information_schema.columns

  Thanks,

  aak

• aakcse

  Valued Member

  Points: 54

  More actions

  February 20, 2009 at 3:50 am

  #947400

  Thanks all,

  I was looking for the below code...

  select column_name,

  table_name,

  data_type,

  case when i.name is not null then 1 else 0 end as is_identity,

  case when k.name is not null then 1 else 0 end as is_pk

  from information_schema.columns c

  left join sys.identity_columns i

  on i.name=c.column_name

  left join sys.key_constraints k

  on k.name=c.column_name

  and k.type='pk'