Procedure To Get Primary Key Columns Names, Ordinal Position and DataType From System Tables BAsed On Table Name

• William Krupinsky JR

  Old Hand

  Points: 392

  More actions

  February 24, 2011 at 12:51 pm

  #246113

  I am trying to find a way to get all 3 details and I am hitting a wall.

  I am using this t-sql 2 get 2 thirds of the data:

  SELECT c.COLUMN_NAME,c.ORDINAL_POSITION

  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS p ,

  INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

  WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

  AND c.TABLE_NAME = p.TABLE_NAME

  AND c.CONSTRAINT_NAME = p.CONSTRAINT_NAME

  AND c.TABLE_NAME = @tblName

  ORDER by c.TABLE_NAME;

  However, no matter how I join in INFORMATION_SCHEMA.COlUMNS view to get the DataType, I always get a row back for each column in the table instead of just the Primary Key Column(s). Can Anyone help?

• Lowell

  SSC Guru

  Points: 323522

  More actions

  February 24, 2011 at 1:42 pm

  #1291268

  William this seems to work for me: maybe you missed the double join?

  SELECT

  c.COLUMN_NAME,

  c.ORDINAL_POSITION,

  cls.DATA_TYPE

  FROM

  INFORMATION_SCHEMA.TABLE_CONSTRAINTS p

  INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

  ON c.TABLE_NAME = p.TABLE_NAME

  AND c.CONSTRAINT_NAME = p.CONSTRAINT_NAME

  INNER JOIN INFORMATION_SCHEMA.COLUMNS cls

  ON c.TABLE_NAME = cls.TABLE_NAME

  AND c.COLUMN_NAME = cls.COLUMN_NAME

  WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

  --AND c.TABLE_NAME = @tblName

  ORDER by c.TABLE_NAME;

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Johan Bijnens

  SSC Guru

  Points: 135504

  More actions

  February 24, 2011 at 1:49 pm

  #1291275

  Just make sure you include the catalog name ( i.e. dbname) in your joins.

  Of course only pick the columns you need. (c.* :ermm: )

  --sp_pkey

  SELECT kcu.COLUMN_NAME

  , kcu.ORDINAL_POSITION

  , c.*

  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS p

  inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu

  on p.CONSTRAINT_TYPE = 'PRIMARY KEY'

  and kcu.TABLE_CATALOG = p.TABLE_CATALOG

  AND kcu.TABLE_SCHEMA = p.TABLE_SCHEMA

  AND kcu.TABLE_NAME = p.TABLE_NAME

  AND kcu.CONSTRAINT_NAME = p.CONSTRAINT_NAME

  inner join information_schema.columns c

  on c.TABLE_CATALOG = p.TABLE_CATALOG

  AND c.TABLE_SCHEMA = p.TABLE_SCHEMA

  AND c.TABLE_NAME = p.TABLE_NAME

  AND c.column_name = kcu.column_name

  --AND kcu.TABLE_NAME = @tblName

  ORDER by kcu.TABLE_NAME ;

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me

• William Krupinsky JR

  Old Hand

  Points: 392

  More actions

  February 24, 2011 at 2:07 pm

  #1291281

  Thanks so much for your help. Works great and you were correct. Thanks again.