SQL Server Performance Forum – Threads Archive

automatd rename of column ref in stored procedures

I seem to recall someone posted a script to automatically update column references in stored procedures, after a column has been renamed. A forum search turned up nothing, maybe it was wishful thinking. Does anyone know of such a script? Google doesnt seem to bring much back Thanks

I usually use a find sproc that joins syscomments and sysobjects, checks syscomments.text for a certain string (in this case column name) and returns a list of objects where string appears. Then I manually replace it. Seems "safer" to me. Bambola.

Hi Bambola. Yes, I do agree, its the safest way. I used your method initially, but sadly I have hundreds to update, necessary because the legacy database schema is ill-concieved and now needs to be ‘consumerised’ as a product. I did have a crack at an autorename procedure, but I realised it wasnt really a 5 minute job when you consider some stored procs are split into several syscomments entries.

I have a query that finds the occurance of a particular string in the database objects (I think for stored procedures currently). This one was written as I had to search for syntax not supported by SQL Server 7.0 when we migrated to same. We could have done with syscomments but the problem with syscomments is that it does not ignore the data written within comments. I think this query can be customized for your requirement as well. So if you need this query, I can post the same. Gaurav

Hi, thanks for the offer, but I just had a much simpler idea. Ive scripted out all the sp’s to a single file, and made my mass modifications in a text editor, before running the script again. Couldnt see the wood for the trees!

Good idea. I couldn’t have done that as I had 2500 procedures with total of close to a million lines of code.
Gaurav

Paul See whether this link http://www.sqlteam.com/item.asp?ItemID=7269] is any help to you. _________
Satya SKJ

This may helps u. Find the dependencies i.e procedures or views associated for the table by using
SP_DEPENDS TABLENAME it will list out the procedures which are assoicated for the
object after that u can change the procedures accordingly. Thanks. Rushendra

SP_Depends a lot on the syscomments table. So if you created a dependent object like sp before the table, it won’t show up in sp_depends. So manual chacking is needed.
Gaurav

]]>