Script to Find Locations of Specified Column

IF OBJECT_ID('dbo.spFindColumn') IS NOT NULL
DROP PROCEDURE dbo.spFindColumn
GO

CREATE PROCEDURE dbo.spFindColumn
@strColName VARCHAR(50)
AS
/**************************************************************************** 
   Creation Date: 03/15/02Created By: Randy Dyess
   Purpose: Determine the location of a column
   Location: All user databases
   Output Parameters: None
   Return Status: None
   Called By: None        
   Calls: None
   Data Modifications: None
   Updates: None                                                                
   Date        Author                      Purpose                                    
   ----------  --------------------------  ---------------------------------
                                                            
****************************************************************************/ 
SET NOCOUNT ON
DECLARE @strSQL NVARCHAR(4000)
DECLARE @lngTableCount INTEGER
DECLARE @strTabName VARCHAR(50)
DECLARE @lngCounter INTEGER

--Create temp table to hold table names
CREATE TABLE #tTableNames
(numID INTEGER IDENTITY(1,1)
,strName VARCHAR(50)
)

--Create temp table to hold stored proc names
CREATE TABLE #tProcNames
(strName VARCHAR(50)
)

--Create temp table to hold view names
CREATE TABLE #tViewNames
(strName VARCHAR(50)
)

--Tables with column
SET @strSQL = 'SELECT so.name AS ''Tables Containing Column: ' + @strColName + '''
FROM sysobjects so
INNER JOIN syscolumns sc
ON so.id = sc.id
WHERE sc.name = @strColName
ORDER BY so.name'

--Show table names
EXEC dbo.sp_executesql @strSQL,N'@strColName VARCHAR(50)',@strColName = @strColName

--Insert table names into temp table
INSERT INTO #tTableNames (strName)
EXEC sp_executesql @strSQL,N'@strColName VARCHAR(50)',@strColName = @strColName
SET @lngTableCount = @@ROWCOUNT
SET @lngCounter = @lngTableCount

--Stored procedures directly referencing column
SET @strSQL = 'SELECT so.name AS ''Stored Procedures Directly Referencing Column: ' + @strColName + '''
FROM dbo.sysobjects so
INNER JOIN dbo.syscomments sc
ON so.id = sc.id
WHERE sc.text LIKE ''%' + @strColName + '%''
AND so.type = ''P''
ORDER BY so.name'

--Show stored procedure names
EXEC dbo.sp_executesql @strSQL,N'@strColName VARCHAR(50)',@strColName = @strColName

--Stored procedures that may indirectly reference column
WHILE @lngCounter <> 0
BEGIN

--Reset table name 
SET @strTabName = (SELECT strName FROM #tTableNames WHERE numID = @lngTableCount)

--Stored procedures indirectly referencing column
SET @strSQL = 'SELECT so.name 
FROM dbo.sysobjects so
INNER JOIN dbo.syscomments sc
ON so.id = sc.id
WHERE ((sc.text LIKE ''%SELECT *%''
OR sc.text LIKE ''%.*%'')
AND sc.text LIKE ''%' + @strTabName + '%''
AND sc.text NOT LIKE ''%SELECT * from #%'')
AND so.type = ''P''
ORDER BY so.name'

--Show stored procedure names
INSERT INTO #tProcNames
EXEC dbo.sp_executesql @strSQL

SET @lngCounter = @lngCounter - 1
END

--Show stored Procedure Names
SET @strSQL = 'SELECT DISTINCT strName AS ''Stored Procedures That May Indirectly Reference Column: ' 
+ @strColName + '''
FROM #tProcNames'
EXEC dbo.sp_executesql @strSQL

--Views directly referencing column
SET @strSQL = 'SELECT so.name AS ''Views Directly Referencing Column: ' + @strColName + '''
FROM dbo.sysobjects so
INNER JOIN dbo.syscomments sc
ON so.id = sc.id
WHERE sc.text LIKE ''%' + @strColName + '%''
AND so.type = ''V''
ORDER BY so.name'

--Show view names
EXEC dbo.sp_executesql @strSQL,N'@strColName VARCHAR(50)',@strColName = @strColName

--Views that may indirectly reference column
SET @lngCounter = @lngTableCount
WHILE @lngCounter <> 0
BEGIN

--Reset table name 
SET @strTabName = (SELECT strName FROM #tTableNames WHERE numID = @lngTableCount)

--Views indirectly referencing column
SET @strSQL = 'SELECT so.name 
FROM dbo.sysobjects so
INNER JOIN dbo.syscomments sc
ON so.id = sc.id
WHERE ((sc.text LIKE ''%SELECT *%''
OR sc.text LIKE ''%.*%'')
AND sc.text LIKE ''%' + @strTabName + '%''
AND sc.text NOT LIKE ''%SELECT * from #%'')
AND so.type = ''V''
ORDER BY so.name'

--Show views names
INSERT INTO #tViewNames
EXEC dbo.sp_executesql @strSQL

SET @lngCounter = @lngCounter - 1
END

--Show views Names
SET @strSQL = 'SELECT DISTINCT strName AS ''Views That May Indirectly Reference Column: ' 
+ @strColName + '''
FROM #tViewNames'
EXEC dbo.sp_executesql @strSQL

DROP TABLE #tTableNames
DROP TABLE #tProcNames
DROP TABLE #tViewNames
GO

--Test
EXEC spFindColumn 'au_id'