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 SMO in Stored Procs

This is a very handy procedure that will return all Views, Stored Procs, Functions and Triggers that contain the string being searched.

I have been working on a 2005 Management Studio addin that uses this procedure and returns the SMO Nodes in a treeview, which you can then double click to open the object to edit/view. Unfortunately it can't be installed into Query Analyser. I am hoping to be able to provide the code for this sometime soon.

-- Set the @searchtext param to the name of the object that you want
-- to find within code of a stored proc.
-- It performs a partial match on the search text by using the % property pre and post
DECLARE @searchtext varchar(50) -- the search text
DECLARE @sql varchar(256) --Building the SQL to run
DECLARE @SearchAllDBs bit --1 = Search All Databases, 0 = Search Current DB

-- Set this
SET @searchtext = ''
SET @SearchAllDBs = 1
if @SearchAllDBs = 0
begin
set @sql = 'SELECT name, type ' set @sql = @sql +
'FROM sysobjects a INNER JOIN syscomments b on a.id = b.id '
end
else
begin
set @sql = 'SELECT ''?'' ''Database'', name, type ' set @sql = @sql +
'FROM ?.dbo.sysobjects a INNER JOIN ?.dbo.syscomments b on a.id = b.id '
end

set @sql = @sql + 'WHERE type in (''U'',''IF'', ''P'', ''V'', ''FN'', ''TF'', ''TR'') '+
'AND b.text LIKE ''%' + @searchtext + '%'' '+
'ORDER BY type, name'

if @SearchAllDBs = 0
exec (@sql)
else
exec sp_MSforeachdb @sql