Locate specific columns used in procs

--LocateAffectedProcedures

IF NOT EXISTS ( SELECT * 
 FROM INFORMATION_SCHEMA.ROUTINES 
 WHERE Routine_Schema = 'dbo' 
 AND Routine_Name = 'LocateAffectedProcedures' 
 AND Routine_Type = 'PROCEDURE')
BEGIN
    EXEC sp_executesql N'CREATE PROCEDURE [dbo].[LocateAffectedProcedures] AS SELECT 1'
END

GO


ALTER PROCEDURE [dbo].[LocateAffectedProcedures]
-- <parameters>
 @in_TableName VARCHAR(50)
 ,@in_Use_spDepends BIT
 ,@in_ColumnName1 VARCHAR(50) = NULL
 ,@in_ColumnName2 VARCHAR(50) = NULL
 ,@in_ColumnName3 VARCHAR(50) = NULL
 ,@in_ColumnName4 VARCHAR(50) = NULL
 ,@in_ColumnName5 VARCHAR(50) = NULL
 ,@in_ColumnName6 VARCHAR(50) = NULL
-- </parameters>
AS
-------------------------------------------------------------------------
-- <scope>Public</scope>
-- <summary>
-- The purpose is to take a table name and list of columns and see if 
-- the columns are found in the body of dependent stored procedures.
-- sp_depends shows which procs depend on the table but not necessarily
-- if the affected column is also contained. This helps reduce the number
-- of objects the DBA must touch.
-- </summary>
--
-- <returns>
-- Recordset - 
-- TableName
-- ,ColumnName
-- ,ProcName
-- ,LineNumber
-- </returns>
-------------------------------------------------------------------------
-- <historylog>
-- <log revision = "1.0" date = "8/22/2007" bug = "" email = "damien.alvarado@gmail.com"> Created </log>
-- <log revision = "1.0" date = "11/21/2007" bug = "" email = "damien.alvarado@gmail.com"> Fixed issue where different schema names caused failure </log>
-- </historylog>

SET NOCOUNT ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
SET QUOTED_IDENTIFIER ON
DECLARE 

@ProcCounter INT
,@ColumnCounter INT
,@ProcName VARCHAR(255)
,@ColumnName VARCHAR(255)
,@LineNumber INT
,@SQL VARCHAR(255)
,@ProcText VARCHAR(MAX)

DECLARE @CheckProcs TABLE
(
CheckProcsId INT IDENTITY
,[name] VARCHAR(257)
,[type] VARCHAR(16)
)

DECLARE @CheckColumns TABLE
(
CheckColumnsId INT IDENTITY
,ColumnName VARCHAR(255)
)

DECLARE @output TABLE
(
TableName VARCHAR(50)
,ColumnName VARCHAR(50)
,ProcName VARCHAR(50)
,LineNumber INT
)

CREATE TABLE #ProcLines
(
ProcLineId INT IDENTITY
,ProcLine VARCHAR(255)
)

--Get procedures that depend on the table
IF @in_Use_spDepends = 'TRUE'
BEGIN
 INSERT INTO @CheckProcs 
 EXEC sp_depends @in_TableName
END
ELSE
BEGIN
 INSERT INTO @CheckProcs
 ([name],[type])
 SELECT Routine_Schema + '.' + Routine_Name, Routine_Type FROM INFORMATION_SCHEMA.ROUTINES WHERE Routine_Type = 'PROCEDURE'
END

--Insert the column names
INSERT INTO @CheckColumns (ColumnName) VALUES (@in_ColumnName1)
IF @in_ColumnName2 IS NOT NULL
 INSERT INTO @CheckColumns (ColumnName) VALUES (@in_ColumnName2)
IF @in_ColumnName3 IS NOT NULL
 INSERT INTO @CheckColumns (ColumnName) VALUES (@in_ColumnName3)
IF @in_ColumnName4 IS NOT NULL
 INSERT INTO @CheckColumns (ColumnName) VALUES (@in_ColumnName4)
IF @in_ColumnName5 IS NOT NULL
 INSERT INTO @CheckColumns (ColumnName) VALUES (@in_ColumnName5)
IF @in_ColumnName6 IS NOT NULL
 INSERT INTO @CheckColumns (ColumnName) VALUES (@in_ColumnName6)

SET @ProcCounter = 1
WHILE @ProcCounter <= (SELECT MAX(CheckProcsId) FROM @CheckProcs)
BEGIN
 SET @ProcName = (SELECT [name] FROM @CheckProcs WHERE CheckProcsId = @ProcCounter)
 DELETE FROM #ProcLines
 DBCC CHECKIDENT ('#ProcLines', RESEED, 1) WITH NO_INFOMSGS
 INSERT INTO #ProcLines 
 EXEC sp_helptext @ProcName
 
 SET @ColumnCounter = 1
 SET @LineNumber = 0
 WHILE @ColumnCounter <= (SELECT MAX(CheckColumnsId) FROM @CheckColumns)
 BEGIN
 SET @ColumnName = (SELECT ColumnName FROM @CheckColumns WHERE CheckColumnsId = @ColumnCounter)
 SET @LineNumber = ( SELECT TOP 1 ProcLineId 
 FROM #ProcLines 
 WHERE ProcLine LIKE '%' + @ColumnName + '%' 
 AND ProcLineId > @LineNumber
 )
 IF @LineNumber IS NOT NULL
 BEGIN
 --Match found add to output, loop counter will not be incremented
 INSERT INTO @output
 (TableName, ColumnName, ProcName, LineNumber)
 VALUES
 (@in_TableName, @ColumnName, @ProcName, @LineNumber)
 END
 IF @LineNumber IS NULL
 BEGIN
 --No matches found, done and move to next column
 SET @ColumnCounter = @ColumnCounter + 1
 
 --Reset LineNumber so will do an accurate search through #ProcLines
 SET @LineNumber = 0
 END
 END
 SET @ProcCounter = @ProcCounter + 1
END

DROP TABLE #ProcLines

SELECT
TableName
,ColumnName
,ProcName
,LineNumber
FROM @output

GO