SQL SERVER – Replace a Column Name in Multiple Stored Procedure All Together

I receive a lot of emails every day. I try to answer each and every email and comments on Facebook and Twitter. I prefer communication on social media as this gives opportunities to others to read the questions and participate along with me. There is always some question which everyone likes to read and remember. Here is one of the questions which I received in email. How to replace a column name in multiple stored procedure efficiently and quickly? I believe the same question will be there any many developers who are beginning with SQL Server. I decided to blog about it so everyone can read it and participate.

“I am beginner in SQL Server. I have a very interesting situation and need your help.

I am beginner to SQL Server and that is why I do not have access to the production server and I work entirely on the development server. The project I am working on is also in the infant stage as well.

In product I had to create a multiple tables and every table had few columns. Later on I have written Stored Procedures using those tables. During a code review my manager has requested to change one of the column which I have used in the table. As per him the naming convention was not accurate. Now changing the columname in the table is not a big issue. I figured out that I can do it very quickly either using T-SQL script or SQL Server Management Studio. The real problem is that I have used this column in nearly 50+ stored procedure. This looks like a very mechanical task. I believe I can go and change it in nearly 50+ stored procedure but is there a better solution I can use. Someone suggested that I should just go ahead and find the text in system table and update it there. Is that safe solution? If not, what is your solution.

In simple words, How to replace a column name in multiple stored procedure efficiently and quickly?

Please help me here with keeping my experience and non-production server in mind.”

Well, I found this question very interesting. Honestly I would have preferred if this question was asked on my social media handles (Facebook and Twitter) as I am very active there and quite often before I reach there other experts have already answered this question. Anyway I am now answering the same question on the blog so all of us can participate here and come up with an appropriate answer. Here is my answer –

“My Friend,

I do not advice to touch system table. Please do not go that route. It can be dangerous and not appropriate.

The issue which you faced today is what I used to face in early career as well I still face it often. There are two sets of argument I have observed – there are people who see no value in the name of the object and name objects like obj1, obj2 etc. There are sets of people who carefully chose the name of the object where object name is self-explanatory and almost tells a story. I am not here to take any side in this blog post – so let me go to a quick solution for your problem.

Note: Following should not be directly practiced on Production Server. It should be properly tested on development server and once it is validated they should be pushed to your production server with your existing deployment practice. The answer is here assuming you have regular stored procedures and you are working on the Development NON Production Server.

Now make sure that Object Explorer Details are open (if not open it by clicking F7). You will see the list of all the stored procedures there. Now you will see a list of all the stored procedures on the right side list. Select either all of them or the one which you believe are relevant to your query. Now…

Right click on the stored procedures >> SELECT DROP and CREATE to >> Now select New Query Editor Window or Clipboard.

Paste the complete script to a new window if you have selected Clipboard option. Now press Control+H which will bring up the Find and Replace Screen. In this screen insert the column to be replaced in the “Find What”box and new column name into “Replace With” box.

Now execute the whole script. As we have selected DROP and CREATE to, it will created drop the old procedure and create the new one.

Another method would do all the same procedure but instead of DROP and CREATE manually replace the CREATE word with ALTER world. There is a small advantage in doing this is that if due to any reason the error comes up which prevents the new stored procedure to be created you will have your old stored procedure in the system as it is. “

Well, this was my answer to the question which I have received. Do you see any other workaround or solution?

There is something called Data Modelling, Logical design and physical design.

Before anyone actually creates table structure in database, one has to create logical design and get approval on the naming convention, data types, references, constraints, indexes etc. After model is approved and is in compliance with the company standards only then start creating physical model (meaning creating tables in the database).

Don’t try to cut steps, if you try to take shortcuts, things like these will happen where in you will have to re-do lots of stuff which is more time consuming. Always follow industry standards and guidelines, these are proven techniques.

Extending Pinal’s answer, what you can do is go to database properties, all tasks, scripts objects, in scripting wizard select all the stored procedure for which you want to change column name, export the query to a new window and then do find and replace, all in once window and execute the script. But make sure you check what you are replacing, sometimes column names are also used in table names, for ex:

Table Name: Product Column Name: ProductId, ProductName

In here, if you try to replace word “Product” with any other word, this will change column name and table name also, one way this could be avoided is by Checking the option, Find exact match (in find options).

Like Pinal said, Never ever update System/MetaData tables. Those are not meant for Developers/DBA to play with.

I don’t think that Drop and Create is a good ideea because: There exists a chance that the stored procedures have specific grant permissionS on them. If do that the rights will be gone. I don’t want to discuss about why to give specific permission on a procedure because is another subject. I will go with ALTER instead of DROP and CREATE

SET NOCOUNT ON GO select distinct ‘sp_helptext ”’+OBJECT_SCHEMA_NAME(id)+’.’+OBJECT_NAME(id)+”’ GO’ from syscomments where text like ‘%%’ Order by ‘sp_helptext ”’+OBJECT_SCHEMA_NAME(id)+’.’+OBJECT_NAME(id)+”’ GO’

Step 3. Now copy the restult and paste in new window. again Press Ctrl+T or change “Result to Text” mode.

Step 4. Copy the result and paste in new window. Execute the query.

Step 5. Copy the result and paste in new window. Now Replace “Text ” with “Go ” with the help of “Find and Replace” in sql. Remember, set the “Match whole word” option true in “Find and Replace”

Step 6: Replace “—————————————————————————————————————————————————————————————————————————————————————” with “” with the help of “Find and Replace” in sql.

Step 7. Now find your searching text in the script, make necessary change and execute this script.

Where schema.objectname is the object or table you are searching for. This way you won’t miss references because someone forgot to put the schema when using the object. Or the other direction if you don’t get false postiives. Searching for “dbo.Customer” finding you “dbo.CustomerAddress” or similar.

Also as of SQL 2005 syscomments is a view included for backwards compatability. It is recommended that you use sys.sql_modules.

It’s amazing that there isn’t yet a well-known and used refactoring tool for TSQL code, like Resharper is for C#. I haven’t used it, but SQL Server Data Tools refactoring support isn’t reliable for this? ).aspx)

Pinal sir i think the best approach would be to open the Stored Procedure in modify mode and do the find and replace. Well i am assuming if the stored procedure have been given specific permission for specific users then we also may need to apply permissions to each SP as in current case more than 50 SP which will be a long way around. :)

@Imran: Very valid comment, especially when doing DB Design & development it is good to use data modelling concepts and practice them, this would help in better implementation of table structures. @Pinal: Valid point about not to mess with System tables.

We can also get all the Stored procedures from System Table using query. SELECT * FROM SYS.SYSCOMMENTS WHERE NUMBER 0

We can take the copy for ‘Text’ field from above query result and replace the column which we want. There is a only one limitation with this method, it can’t hold the very large Stored procedure as Data Type for this column is nvarchar(4000) .

Hello Dave, I agree to what Gatej Alexandru said that Drop and Create is not a good idea because, there is a great possibility that the stored procedures have specifically granted permission for them. If you do that, the rights will be gone. ALTER will do instead of DROP and CREATE.

Pinal Dave is a technology enthusiast and an independent consultant. He has authored 11 SQL Server database books, 21 Pluralsight courses and have written over 3800 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 14+ years of hands on experience he holds a Masters of Science degree and a number of database certifications. For any SQL Server Performance Tuning Issue send email at pinal @ sqlauthority.com .

Nupur Dave is a social media enthusiast and and an independent consultant.