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.
Go to Server Note >> Databases >> DatabaseName >> Programmability >> Stored Procedure
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?
Reference : Pinal Dave (https://blog.sqlauthority.com)
21 Comments. Leave new
@ Person who asked question.
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.
~ IM.
Maybe in a perfect world, where you don’t build prototypes and make a final product out of them. And where there are no changes in logical model after creating the physical one…
Hello Pinal,
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
What do you think?
@Gatej,
100% agreed. But there are also options that you can set for scripting to include object level permissions when you generate scripts from ssms.
If you have your object permission scripted out then you can do Drop and create.
~IM.
Normally i do this process in below steps
Step 1. Press Ctrl+T or change “Result to Text” mode.
Step 2. Execute below comand. Replace “” with text, you want to search in store procedures.
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.
Great suggestion. Exactly what I was thinking. My only change would be (assuming a SQL 2008 or higher instance) for step #2 use the following query:
SELECT 'sp_helptext [' + referencing_schema_name + '.' + referencing_entity_name + ']' FROM sys.dm_sql_referencing_entities('schema.objectname','OBJECT')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.
Quick correction. My query works if you are going to run each row of output one at a time. If you want to run them all at once do the following:
SELECT 'EXEC sp_helptext [' + referencing_schema_name + '.' + referencing_entity_name + ']' FROM sys.dm_sql_referencing_entities('schema.objectname','OBJECT')Script whole database. Not only procedures but functions, triggers, views (SSMS -> your database -> right click – > Tasks -> Generate scripts…”
or use Red Gate’s SQL Search (free tool) to find related objects
**Validate each replace by hand.**
Biggest issue is client applications. You may need modify SELECTs with NewField AS OldField.
Usually missed code:
– reports (with embedded queries)
– linked server’s code
– server agent jobs
– client code or procedures with dynamically created SQL
Script whole database. Not only procedures but functions, triggers, views (SSMS -> your database -> right click – > Tasks -> Generate scripts…”
or use Red Gate’s SQL Search (free tool) to find related objects
**Validate each replace by hand.**
Biggest issue is client applications. You may need modify SELECTs with NewField AS OldField.
Usually missed code:
– reports (with embedded queries)
– linked server’s code
– server agent jobs
– client code or procedures with dynamically created SQL
I like to use SQL Search from RedGate (100% free to identify all the procedures/views/functions that contain that column and start from here.
We can use simple query to find existence of a word in a Procedure by
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ‘%%’
and Vinay’s Idea also appreciated .
I believe we no need to depend a third party tool to do these process , unless and until Sql server provided these kind of ways …
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.
Hi Pinal,
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) .
can i replace same column name in no of stored procedure .not single stored procedure at a time..
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.
My last paragraph says the same.
I want to add dbo as prefix on each table which are using in any sp or funcation
If I create one stored procedure for one database, can I run it on the other databases on the same server or different if the fields, tables, schemas are same on both the databases?
If there is no relation between both the databases then also can I run it?
How to modify column ……. in all ‘Sp_depends column ‘ list