Find Column Name Usage

,

This procedure produces a formatted report to list which tables, views and table functions have a column of the specified name. This is particularly useful in systems that do not enforce referential integrity or when schema changes are being evaluated.

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


CREATE PROCEDURE [dbo].[usp_FindColumnNameUsage]
	@vcColumnName varchar(100) 
AS
/************************************************************************************************
DESCRIPTION:	Creates prinatable report of all tables, views, 
		and table user-defined functions that have a column
		named the same as the column passed to proc
	
PARAMETERS:
		@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_FindColumnNameUsage 'subject_id'
	
AUTHOR:	Karen Gayda

DATE: 07/19/2007

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



PRINT ''
PRINT 'REPORT FOR COLUMN:'
PRINT '-----------------------------------------'
PRINT @vcColumnName


PRINT ''
PRINT ''
PRINT 'TABLES:'
PRINT ''

SELECT DISTINCT  SUBSTRING(o.NAME,1,60) AS [Table Name]
		FROM sysobjects o
		INNER JOIN syscolumns c
			ON o.ID = c.ID
		WHERE c.name = @vcColumnName
			AND o.XTYPE = 'U'
	ORDER BY  [Table Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' tables for column ' + @vcColumnName +  '.'



PRINT''
PRINT''
PRINT 'VIEWS:'
PRINT''

SELECT DISTINCT  SUBSTRING(o.NAME,1,60) AS [View Name]
		FROM sysobjects o
		INNER JOIN syscolumns c
			ON o.ID = c.ID
		WHERE c.name = @vcColumnName
			AND o.XTYPE = 'V'
	ORDER BY  [View Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' views for column ' + @vcColumnName 


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

SELECT DISTINCT  SUBSTRING(o.NAME,1,60) AS [Function Name]
	FROM sysobjects o
		INNER JOIN syscolumns c
			ON o.ID = c.ID
		WHERE c.name = @vcColumnName
			AND o.XTYPE = 'TF'
	ORDER BY  [Function Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' table functions for column ' + +@vcColumnName +  '.'


GO

Rate

4.17 (6)

Share

Share

Rate

4.17 (6)