Getting the first column from each table

• adish

  SSChasing Mays

  Points: 602

  More actions

  February 6, 2009 at 3:49 am

  #199682

  Hi

  I want to retrieve the first column from all the tables in a MS SQL 2005 db and I also want to know whether it is a PK or not.

  I can get the PK for the tables using this:

  select a.Table_Name, a.Column_Name from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE a,

  INFORMATION_SCHEMA.TABLE_CONSTRAINTS b

  where b.constraint_type='PRIMARY KEY'

  The problem with the above is that although I can get the PK for all the tables, but it does not tell me if this PK was the first column or not. My issue might seem silly because the order of the columns in a table really should not matter, but I will try to explain the rationale behind it (not now but some other time).

  Thanks in advance

  Adish

• raj acharya

  SSCrazy

  Points: 2805

  More actions

  February 6, 2009 at 6:18 am

  #939416

  man......... you realy put a thing that keep me in thinking ok you will get your answer

  Raj Acharya

• raj acharya

  SSCrazy

  Points: 2805

  More actions

  February 6, 2009 at 6:27 am

  #939423

  here is your answer try my best to do this

  SELECT OBJECT_NAME(a.OBJECT_ID) AS tablename ,a.NAME AS first_column_name,a.column_id,b.colid,b.indid,

  CASE WHEN b.indid = 1 THEN 'PRIMARY KEY ' WHEN b.indid= 2 THEN 'NON CLUSTERED INDEX AND NOT PRIMARYKEY' ELSE 'NOT PRIMARY KEY 'END AS key_desc

  FROM sys.columns AS a

  INNER join sys.sysobjects AS c ON a.[OBJECT_ID] =c.id

  FULL OUTER JOIN SYS.sysindexkeys AS b

  ON a.column_id = b.colid and a.[object_id]=b.id

  WHERE OBJECT_NAME(a.OBJECT_ID) NOT LIKE 'sys%'

  AND a.column_id = 1

  AND c.xtype = 'U'

  Raj Acharya

• adish

  SSChasing Mays

  Points: 602

  More actions

  February 6, 2009 at 6:58 am

  #939467

  Perfect

  Thanks a lot, Raj.

  Adish