SQLAuthority News – Follow up on – Replace a Column Name in Multiple Stored Procedure all together

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.

  1. Step 1. Press Ctrl+T or change “Result to Text” mode.
  2. 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.
  3. Step 3. Now copy the result and paste in new window. Again Press Ctrl+T or change “Result to Text” mode.
  4. Step 4. Copy the result and paste in new window. Execute the query.
  5. Step 5. Copy the result and paste in new window.
  6. Step 6. Now find your searching text in the script, make necessary changes and execute this script.
  7. 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 (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Preserve Leading Zero While Coping to Excel from SSMS
Next Post
SQL SERVER – Solution – 2 T-SQL Puzzles – Display Star and Shortest Code to Display 1

Related Posts

No results found.

3 Comments. Leave new

  • visakh murukesan
    October 1, 2012 11:46 pm

    This has a limitation. It wont return any dependency where object has been used inside a dynamic sql string

    see this small example

    CREATE PROCEDURE proctest1

    EXEC(‘SELECT * FROM YourTable’)

    CREATE PROCEDURE proctest1

    SELECT * FROM YourTable

    –finding dependency using two conventional methods
    sp_depends ‘YourTable’

    SELECT ‘EXEC sp_helptext [‘ + referencing_schema_name + ‘.’ + referencing_entity_name + ‘]’
    FROM sys.dm_sql_referencing_entities(‘YourTable’,’OBJECT’)

    both of the above doesnt return proctest as table was used inside dynamic string in it. In such cases I rely upon below query

    select definition from sys.sql_modules where definition like ‘% Employee%’

    and I’ve blogged about that as well here

  • Hardik Turakhia
    October 19, 2012 12:12 pm

    I need to search all content of my database object(only SPs) but the challenge is i have to ignore the commented part??
    The same thing i have to do for almost 60 DBs and each DB approx contain at least 300 SPs.
    Any good way to do this??

  • Hi PinalDave –

    In your previous post the “Alter to” is not available in the picture. Do you know why this is grayed out, or not available as an option?

    I thought it was permissions, but gave myself db_owner of the database and the resulting menu still appears the same.

    Any ideas how to script the objects as “Alter to”?

    Any feedback is much appreciated.

    Thank you,


Leave a Reply