Technical Article

Index Information with column name in whole databs


Through this script we can fetch on regular index information with whole database. Here i am use temporary tebale concept show no procedure so its run quickly and also fetch information from production database in uptime. No sytem generate index information not send me.

-- authour Rajib Kundu 
-- Designation sql Dba in Egain Communication Pvt Ltd
use <databasename> -- put database name from where u want index information.
set nocount on

create table #TmpOutPut([Table Name] varchar(100),[Index Name] varchar(100),[Column Name] varchar(100),indid int,keyno int,IsPrimary varchar(7))


Insert into #TmpOutPut

Select as table_name, as constname , as [Col Name],k.indid,k.keyno,


           when exists(select 1 from sysobjects where name = then 'True'

           else 'False'

       end as [Primary]   

from sysobjects p

join sysindexes si on

join sysindexkeys k on

join syscolumns c on and k.colid =c.colid


and p.status > 0

and not like '_wa_sys%'

and si.impid <> -1

order by,,k.indid,k.keyno


--drop unnecessary columns

Alter table #TmpOutPut 

  drop column indid ,keyno


--show the final output

exec('select * from  #TmpOutPut') 


drop table  #TmpOutPut 


set nocount off


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating