This is a script that I've created because I was so impressed with Visio's database diagrams. In Visio if you reverse engineer a database diagram, you can see all the indexes represented in the diagram - really useful if you're doing an initial index review. Anyway, the script will return a list of table columns and associated indexes.

create Proc spExaminIndexes
/*****************************************************
Created by: Paul Ibison (www.replicationanswers.com)
Modified by: PI on 14/12/2005 to remove certain typos
Date: 26th November 2005
Use: To enumerate all tables, columns and indexes in one recordset.
******************************************************/
as
set nocount on


declare @objname varchar(2000)
declare @objid int
declare @indid smallint
declare @indname varchar(2000)
declare @keys varchar(2000)
declare @i int
declare @thiskey varchar(2000)

if object_id('tempdb..#tmpIndexTable') is not null
drop table #tmpIndexTable

-- create temp table
create table #tmpIndexTable
(
index_name varchar(2000) NULL,
index_keys varchar(2000) NULL,
table_name varchar(2000) NULL
)


DECLARE tables_cursor CURSOR FOR
SELECT table_name FROM information_schema.tables where table_type = 'base
table'
ORDER BY table_name

OPEN tables_cursor

FETCH NEXT FROM tables_cursor
INTO @objname

WHILE @@FETCH_STATUS = 0
BEGIN

set @objid = object_id(@objname)

declare index_cursor cursor local static for
select indid, name from sysindexes
where id = @objid and indid > 0 and indid < 255 and (status & 64)=0 order
by indid
open index_cursor
fetch index_cursor into @indid, @indname

-- if no index, move on and just record the tablename
if @@fetch_status < 0
begin
insert into #tmpIndexTable(index_name,index_keys,table_name)
values (null,null,@objname)
goto next
end

set @i = 0
set @thiskey = ''

-- check out each index, figure out its type and keys
while @@fetch_status >= 0
begin
select @keys = index_col(@objname, @indid, 1), @i = 2
select @thiskey = index_col(@objname, @indid, @i)

insert into #tmpIndexTable (index_name,index_keys,table_name)
values (@indname, @keys, @objname)

while (@thiskey is not null )
begin
insert into #tmpIndexTable (index_name,index_keys,table_name)
values (@indname, @thiskey, @objname)
select @keys = @keys + ', ' + @thiskey, @i = @i + 1
select @thiskey = index_col(@objname, @indid, @i)
end

-- next index
fetch index_cursor into @indid, @indname
end

next:
close index_cursor
deallocate index_cursor
FETCH NEXT FROM tables_cursor INTO @objname
END

CLOSE tables_cursor
DEALLOCATE tables_cursor

--gets single-column and multi-column indexes
select information_schema.columns.table_name,
information_schema.columns.column_name,
isnull(#tmpIndexTable.index_name,'') as index_name
from information_schema.columns
inner join information_schema.tables on
information_schema.columns.table_name =
information_schema.tables.table_name
left outer join #tmpIndexTable on #tmpIndexTable.table_name =
information_schema.columns.table_name
and #tmpIndexTable.index_keys = information_schema.columns.column_name
where information_schema.tables.table_type = 'base table'
order by information_schema.columns.table_name,
information_schema.columns.column_name