T-Sql question.

• Tac11

  SSCertifiable

  Points: 6980

  More actions

  August 6, 2015 at 9:00 am

  #320311

  When I run below query I get 3 columns, but when I try to add table name

  ind.object_name (object_id) it's giving me an error "Ambiguous column name 'object_id"

  How do I add tables name with 3 columns?

  SELECT ind.name, ic.index_id , ind.type_desc from sys.indexes ind

  INNER JOIN

  sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id

  INNER JOIN

  sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id

  INNER JOIN

  sys.tables t ON ind.object_id = t.object_id

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  August 6, 2015 at 9:02 am

  #1817890

  Tac11 (8/6/2015)

  ---

  When I run below query I get 3 columns, but when I try to add table name

  ind.object_name (object_id) it's giving me an error "Ambiguous column name 'object_id"

  How do I add tables name with 3 columns?

  SELECT ind.name, ic.index_id , ind.type_desc from sys.indexes ind

  INNER JOIN

  sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id

  INNER JOIN

  sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id

  INNER JOIN

  sys.tables t ON ind.object_id = t.object_id

  Add the alias to the object_id column reference

  😎

  SELECT OBJECT_NAME(ind.object_id) AS OBJNAME,ind.name, ic.index_id , ind.type_desc from sys.indexes ind

  INNER JOIN

  sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id

  INNER JOIN

  sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id

  INNER JOIN

  sys.tables t ON ind.object_id = t.object_id

• Y.B.

  SSChampion

  Points: 11649

  More actions

  August 6, 2015 at 9:05 am

  #1817893

  Because you have multiple instances of object_id and you didn't specify which one to use. Just add the alias and it should work.

  ---

  SELECT quote FROM brain WHERE original = 1
  0 rows returned

• Tac11

  SSCertifiable

  Points: 6980

  More actions

  August 6, 2015 at 9:07 am

  #1817894

  Thank you!!!

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  August 6, 2015 at 9:57 am

  #1817914

  Tac11 (8/6/2015)

  ---

  When I run below query I get 3 columns, but when I try to add table name

  ind.object_name (object_id) it's giving me an error "Ambiguous column name 'object_id"

  How do I add tables name with 3 columns?

  SELECT ind.name, ic.index_id , ind.type_desc from sys.indexes ind

  INNER JOIN

  sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id

  INNER JOIN

  sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id

  INNER JOIN

  sys.tables t ON ind.object_id = t.object_id

  Your query:

  SELECT

  ind.name,

  ic.index_id,

  ind.type_desc

  from

  sys.indexes ind

  INNER JOIN

  sys.index_columns ic

  ON ind.object_id = ic.object_id and

  ind.index_id = ic.index_id

  INNER JOIN

  sys.columns col

  ON ic.object_id = col.object_id and

  ic.column_id = col.column_id

  INNER JOIN

  sys.tables t

  ON ind.object_id = t.object_id;

  Adding the table name to the query:

  SELECT

  t.name TableName, -- No need for the object_name function

  ind.name,

  ic.index_id,

  ind.type_desc

  from

  sys.indexes ind

  INNER JOIN

  sys.index_columns ic

  ON ind.object_id = ic.object_id and

  ind.index_id = ic.index_id

  INNER JOIN

  sys.columns col

  ON ic.object_id = col.object_id and

  ic.column_id = col.column_id

  INNER JOIN

  sys.tables t

  ON ind.object_id = t.object_id;