Locate specific columns used in procs
Ever inherit a DB and wonder how many procs/views/triggers are referencing a column you have to make a DDL change to? You can change from VARCHAR(10) to VARCHAR(100), and everything looks cool until people start complaining about data being truncated. Where is that sp parameter definition that is truncating the data? This should help reduce your search efforts.
sp_depends shows you affected objects by table but not by column so I wrote this to show me where a particular column is getting used.
Also since sp_depends may not return all related procs I put a flag in to basically search all procedures.
Here is a sample call against the AdventureWorks DB.
USE [AdventureWorks]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[LocateAffectedProcedures]
@in_TableName = N'HumanResources.Employee',
@in_Use_spDepends = FALSE,
@in_ColumnName1 = N'LoginId'
SELECT 'Return Value' = @return_value
GO
When you see the output you will see that the column is used in only one Stored Procedure and which lines it is used on. sp_depends returns 18 entries and searching through them all would be a bummer.
Enjoy!
--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 
