Last month I had a fantastic time with lots of puzzles and brain teasers, the amount of participation which I have received on the blog is indeed inspiring to write more. One of the blog post was about how to replace a column name in all the stored procedures. The article had very interesting conversation as a follow up. Please read the original article Replace a Column Name in Multiple Stored Procedure all together before reading this blog further as they are connected.
Let us start few of the interesting comments. SQL Server Expert Imran Mohammed had a wonderful first and excellent note. I suggest all of you to read it. Imran stresses on the Data Modelling and Logical as well as Physical Design. Developers must create a logical design and get approval on naming convention, data types, references, constraints, indexes etc. He further suggested that one should not cut steps but must follow all the industry standards and guidelines. Here extended my blog post with following note – “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 and Column Name: ProductId, ProductName”. Thanks Imran Great Points!
Gatej Alexandru suggested that it is not good idea to DROP or CREATE but rather use ALTER as quite possible there may be permissions issue as well. Very good point let me see if I can write blog post over it.
Vinay Kumar and SQLStudent144 have proposed another method to achieve the same. I am combining their solution and writing them here.
Step 1. Press Ctrl+T or change “Result to Text” mode.
Step 2. Execute below commands.SELECT 'EXEC 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.
Step 3. Now copy the result 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.
Step 6. Now find your searching text in the script, make necessary changes and execute this script.
Do not forget to remove the code which is generated in resultset which are not relevant to the T-SQL Script.
Digitqr suggest we can do this for other objects besides Stored Procedure as well. Iosif suggests to use tool SQL Search from RedGate.
I guess this sums it well. We have an alternative perspective to our original issue of replacing the column name in multiple stored procedure.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology
