Find Procedures and tables by column

If exists (Select * from sysobjects where name = 'FindObjByColumn_sp' and type = 'P')
drop proc FindObjByColumn_sp 
go

Create Proc FindObjByColumn_sp@Column varchar(30) 
/******************************************************************************************************

Procedure Name: FindObjByColumn_sp
Purpose :Search the database for all tables and all procedures that use a specific column.
Called by:N/A

Input Params:@Column - stores the column name that user is searchin for
Output Params:None
Return Values:

Written By:PNE 08/04/2002
Syntax:FindObjByColumn_sp 'DistributionChannel'
Change history:

********************************************************************************************************/--SYNTAX:
AS
Declare @ssql varchar(100)

--Add wildcard so that seach can be performed even if only part of the column name is used
Select @Column = @Column + '%'

SET NOCOUNT ON

select @ssql = 'Tables that contain columns that begin with ' + @Column
select @ssql
print '----------------------------------------------------------'
SELECT convert(varchar(60), O.NAME) "Table Name" 
FROM SYSCOLUMNS C, SYSOBJECTS O, master.dbo.systypes T 
WHERE C.ID = O.ID AND C.xtype = T.xtype
AND C.NAME LIKE @Column
and O.Type = 'U'
group by o.name

--For stored procedures, add wildcard to beginning because column will be contained within text
Select @Column =  '%' + @Column 

select @ssql = 'Procs that contain the word ' + replace(@Column, '%', '')
select @ssql
print '----------------------------------------------------------'
select convert(varchar(60), O.NAME) "Proc Name"
from sysobjects o, syscomments c
where o.id = c.id
and c.text like @Column

SET NOCOUNT OFF