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

Rate

5 (1)

Share

Share

Rate

5 (1)