Ads

19 March 2013

Query to list the computed columns indexes

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

No comments:

Post a Comment