Find all Columns in Indexes

This proc will list all the indexes and the columns that are in the indexes.
Yes you can view this from the Table object but this is a little quicker.

select
a1.name TableName
, b1.name IndexName
, d1.name ColumnName
from sysobjects a1
inner join sysindexes b1
on a1.id = b1.id
inner join sysindexkeys c1
on a1.id = c1.id
and b1.indid = c1.indid
inner join syscolumns d1
on a1.id = d1.id
and c1.colid = d1.colid
where b1.name not like '_WA%' --Remove statistics indexes
--and a1.name like '' --To specify a table
order by a1.name, b1.name

Find UnIndexed Foreign Keys

The following script will find all the foreignkeys on tables that are not currently apart of an Index for the table. This can be used to identify possible additional indexes that may need to be added to help with performance. Not all FKs need to be in an index, but it may point out some that really should be.

select
a1.name TableName
, a2.name FKName
, c1.name ColumnName
from sysobjects a1
inner join sysforeignkeys b1
on a1.id = b1.fkeyid
inner join sysobjects a2
on a2.id = b1.constid
inner join syscolumns c1
on c1.id = a1.id
and c1.colid = b1.fkey
where not exists (
select *
from sysobjects a
inner join sysindexes b
on b.id = a.id
inner join sysindexkeys k
on k.id= a.id
and k.indid = b.indid
inner join syscolumns c
on c.id = a.id
and c.colid = k.colid
where a.name not like 'sys%'
and b.name not like '_WA%'
and c.colid = b1.fkey
and c.id = a1.id
)
order by
a1.name
, a2.name
, c1.name

Performance Tuning Stored Procedures

Naming Conventions
It is strongly recommended that you do not create any stored procedures using sp_ as a prefix. SQL Server always looks for a stored procedure beginning with sp_ in this order:
The stored procedure in the master database.
The stored procedure based on any qualifiers provided (database name or owner).
The stored procedure using dbo as the owner, if one is not specified.
Therefore, although the user-created stored procedure prefixed with sp_ may exist in the current database, the master database is always checked first, even if the stored procedure is qualified with the database name. Important: If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.
Schema prefixing
Always reference your database objects using the schema name as a prefix. For the same reason as in the Naming Conventions tip, when looking for a database object for the first time, SQL Server will look in a specific order. But by prefixing the database object in question, it can find it straight away.
e.g.
Select name from tableA where Id = 2
Becomes,
Select name from dbo.tableA where Id = 2
Using database functions become,
Select dbo._fn_GetName(@ID, dbo.tableA)
Using joins become,
Select Id, Name, Address From dbo.Customer Left Outer Join dbo.Address on Customer.Id = Address.Id