Find Column Usage

,

When changing schema or modifying an application it is often necessary to determine when a column from a specific table is used.  This procedure produces a formatted report (when outputting from query analyzer or management studio in text mode) that specifies all of the procedures, views, functions and triggers that use the designated table.column.

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

Rate

5 (13)

Share

Share

Rate

5 (13)