Computed columns

• M&M

  SSC-Insane

  Points: 21700

  More actions

  May 11, 2009 at 4:58 pm

  #133861

  Is there any way to find tables that have indexes on computed columns. Any queries for the same? Please advice.

  M&M

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 11, 2009 at 5:05 pm

  #992561

  syscolumns has a iscomputed column...the calculation is stored in syscomments if you want to join to get that:

  now to get the indexes, that i'm not sure yet....

  select object_name(syscolumns.id),

  syscolumns.name As CalculatedColumn ,

  syscomments.text as TheCalculation

  from syscolumns

  inner join syscomments on syscolumns.id=syscomments.id

  where iscomputed 0

  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!

• M&M

  SSC-Insane

  Points: 21700

  More actions

  May 11, 2009 at 5:15 pm

  #992564

  But is there a way to get the index on a computed column?

  M&M

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 11, 2009 at 5:26 pm

  #992568

  this seemed to work for me, but i only had one table with no index:

  select distinct object_name(syscolumns.id),

  syscolumns.name As CalculatedColumn ,

  syscomments.text as TheCalculation,

  isnull(object_name(SYSINDEXKEYS.id),'No Index Using This CalculatedColumn') As IndexName

  from syscolumns

  inner join syscomments on syscolumns.id=syscomments.id

  left outer join sysindexes

  on syscolumns.id=sysindexes.id

  left outer join SYSINDEXKEYS ON sysindexes.ID=SYSINDEXKEYS.ID

  AND sysindexes.INDID=SYSINDEXKEYS.INDID

  and SYSINDEXKEYS.COLID=SYSCOLUMNS.COLID

  where iscomputed 0

  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!