Finding column references in SQL Server

Here is a stored procedure that will generate a text formatted report that details what views, stored procedures, functions and triggers use the specified column. This is useful to know when you make a change to a column. This information is available from the UI, but this is a nice SQL way of doing it. This solution was copied from http://www.sqlservercentral.com/scripts/Miscellaneous/31963/. Thank you!

if exists (select * from dbo.sysobjects
 where id = object_id(N'[dbo].[usp_FindColumnUsage]')
  and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_FindColumnUsage]
GO

CREATE PROCEDURE [dbo].[usp_FindColumnUsage]
 @vcTableName varchar(100),
 @vcColumnName varchar(100)
AS
/************************************************************************************************
DESCRIPTION: Creates prinatable report of all stored procedures, views, triggers
  and user-defined functions that reference  the
  table/column passed into the proc.

PARAMETERS:
  @vcTableName - table containing searched column
  @vcColumnName - column being searched for
REMARKS:
  To print the output of this report in Query Analyzer/Management 
  Studio select the execute mode to be file and you will
  be prompted for a file name to save as. Alternately
  you can select the execute mode to be text, run the query, set
  the focus on the results pane and then select File/Save from
  the menu.

  This procedure must be installed in the database where it will
  be run due to it's use of database system tables.

USAGE:
  
  usp_FindColumnUsage 'jct_contract_element_card_sigs', 'contract_element_id'

AUTHOR: Karen Gayda

DATE: 07/19/2007

MODIFICATION HISTORY:
WHO  DATE  DESCRIPTION
---  ---------- -------------------------------------------
*************************************************************************************************/
SET NOCOUNT ON

PRINT ''
PRINT 'REPORT FOR DEPENDENCIES FOR TABLE/COLUMN:'
PRINT '-----------------------------------------'
PRINT  @vcTableName + '.' +@vcColumnName

PRINT ''
PRINT ''
PRINT 'STORED PROCEDURES:'
PRINT ''

SELECT DISTINCT  SUBSTRING(o.NAME,1,60) AS [Procedure Name]
  FROM sysobjects o
  INNER JOIN syscomments c
   ON o.ID = c.ID
  WHERE  o.XTYPE = 'P'
   AND c.Text LIKE '%' + @vcColumnName + '%' + @vcTableName + '%' 
  
  
 ORDER BY  [Procedure Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent stored procedures for column "' + @vcTableName + '.' +@vcColumnName +  '".'

PRINT''
PRINT''
PRINT 'VIEWS:'
PRINT''
SELECT DISTINCT  SUBSTRING(o.NAME,1,60) AS [View Name]
  FROM sysobjects o
  INNER JOIN syscomments c
   ON o.ID = c.ID
  WHERE  o.XTYPE = 'V'
   AND c.Text LIKE '%' + @vcColumnName + '%' + @vcTableName + '%' 
  
  
 ORDER BY  [View Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent views for column "' + @vcTableName + '.' +@vcColumnName +  '".'

PRINT ''
PRINT ''
PRINT 'FUNCTIONS:'
PRINT ''

SELECT DISTINCT  SUBSTRING(o.NAME,1,60) AS [Function Name],
  CASE WHEN o.XTYPE = 'FN' THEN 'Scalar'
   WHEN o.XTYPE = 'IF' THEN 'Inline'
   WHEN o.XTYPE = 'TF' THEN 'Table'
   ELSE '?'
  END
  as [Function Type]
  FROM sysobjects o
  INNER JOIN syscomments c
   ON o.ID = c.ID
  WHERE  o.XTYPE IN ('FN','IF','TF')
   AND c.Text LIKE '%' + @vcColumnName + '%' + @vcTableName + '%' 
  
  
 ORDER BY  [Function Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent functions for column "' + @vcTableName + '.' +@vcColumnName +  '".'

PRINT''
PRINT''
PRINT 'TRIGGERS:'
PRINT''

SELECT DISTINCT  SUBSTRING(o.NAME,1,60) AS [Trigger Name]
  FROM sysobjects o
  INNER JOIN syscomments c
   ON o.ID = c.ID
  WHERE  o.XTYPE = 'TR'
   AND c.Text LIKE '%' + @vcColumnName + '%' + @vcTableName + '%' 
  
  
 ORDER BY  [Trigger Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent triggers for column "' + @vcTableName + '.' +@vcColumnName +  '".'

GO