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