CODEWITHSUNDEEP
sql-serversql-server-2008stored-procedures
Chad Portman
Chad Portman

Reputation: 1216

Checking if any stored procedure on a database modifies a certain column

Is there a way to write a query that would show me the names of any stored procedure on my database that modifies a certain column? This database at work has over 1300 stored procedures listed under System stored procedures and then two others that are not listed under that. I need to know which one modifies the column "CustomFlag 1".

For some reason every 6 months this column is being cleared out for every entry so my assumption is that a stored procedure is doing this and would like to figure out which one so we can fix it.

This is on SQL Server 2008 using SSMS 2008.

Upvotes: 0

Views: 92

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can try using the sys.sql_modules system view to get all sprocs referencing the column that is of interest to you. Field definition of the view returns the SQL text that defines the sproc.

As an example, the following query: 

SELECT obj.Name SPName, sm.definition SPText
FROM sys.sql_modules sm
INNER JOIN sys.objects obj ON sm.object_id = obj.OBJECT_ID
WHERE sm.definition LIKE '%' + '[JobTitle] =' + '%' AND TYPE = 'P'

returns all sprocs trying to set field JobTitle in the AdventureWorks2008R2 database:

SPName                      SPText
--------------------------------------------------------------------------------------
uspSearchCandidateResumes   CREATE PROCEDURE [dbo].[uspSearchCandidateResumes] ...etc

Upvotes: 1

Grax32
Grax32

Reputation: 4059

Generate a script of your database schema and do a text search within it for "CustomFlag"

You can script out the entire schema (or just the stored procs) by right-clicking on the database name in Sql Server Management Studio and select Tasks/Generate Scripts

Upvotes: 1

Related Questions