How to stop stored procs from whining about a missing column that I am about to delete in SQL Server 2008?

Question

Hi all,

I am deleting a column from one of the frequently used tables in my database. Last time I did this errors started to crawl up from all sorts of stored procs that I had long forgotten existed; complaining about the missing column. (only when that stored proc was called)

so, I dont want to get winded up with these things again, I want to make sure all stored procs are free of that column before I actually delete it.

What is the best way to search through all stored procs (and I have quite a lot of them) and remove the reference to that column?

I tried to find an option in the menu to do this but I did not find anything very obvious to do this.

any help, (other than telling me to go through them all one by one) is appreciated.
ps: of course, doesnt mean that I will depreciate your comment if you do tell me. I will only downvote :P
(nah, just kidding!)

Answer 1

hi,
use this script.It will also return triggers. If many tables has column with the same name you can add tale name to the where too. This script works on MSSQL 2000, 2005 i heavent tested it on 2008 but it should work fine too

SELECT o.name
FROM sysobjects o
    INNER JOIN syscomments c ON o.id = c.id
WHERE c.text like '%column_name%'

Edit: If you want to filter it only to store procedures add AND type ='P' to the where clause

Best Regards,
Iordan

Answer 2

EDIT: sorry, my bad. here's the code for searching within the stored procedure's code

The following stored procedure should be able to list all the stored procedures whose text contain the desired string (so, place your column name in it and fire away):

CREATE PROCEDURE Find_Text_In_SP
@StringToSearch varchar(100) 
AS 
   SET @StringToSearch = '%' +@StringToSearch + '%'
   SELECT Distinct SO.Name
   FROM sysobjects SO (NOLOCK)
   INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
   AND SO.Type = 'P'
   AND SC.Text LIKE @stringtosearch
   ORDER BY SO.Name
GO

Usage:

exec Find_Text_In_SP 'desired_column_name'

Source here

Answer 3

To add to the various TSQL solutions, there is a free tool from Red Gate that integrates into SSMS: SQL Search

Answer 4

You can use Dependence option for that table to find the Dependent object or list of Procedure or function which are depend on this table. Use below script

sp_depends 'TableName'

another option is create script for that column containing but that will filter all the text in the procedure or function.

Answer 5

If you use MS SQL later than version 2000, it's better to search sys.sql_modules rather than sys.syscomments, since syscomments only hold records of nvarchar(4000), and the text you are looking for may be split into two records.

So while you can use a query like this from MSDN

SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, o.type, o.type_desc, sm.definition
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
WHERE sm.definition like '%' + @ColumnName + '%'
ORDER BY o.type;

you should be aware that this search finds any procedure containing that text, regardless of whether it is an actual column name and which table the column belongs to.