Find Column Name Usage

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:
WHODATEDESCRIPTION
--------------------------------------------------------
*************************************************************************************************/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