Index Information with column name in whole databs

-- authour Rajib Kundu 
-- Designation sql Dba in Egain Communication Pvt Ltd
--email Rk_india1@rediffmail.com
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  p.name as table_name,

       si.name as constname ,c.name as [Col Name],k.indid,k.keyno,

       case 

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

           else 'False'

       end as [Primary]   

from sysobjects p

join sysindexes si on p.id=si.id

join sysindexkeys k on p.id=k.id

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

AND K.INDID = SI.INDID

and p.status > 0

and si.name not like '_wa_sys%'

and si.impid <> -1

order by p.name,si.name,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