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 + ']'
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 (https://blog.sqlauthority.com)
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
SELECT ‘EXEC sp_helptext [‘ + referencing_schema_name + ‘.’ + referencing_entity_name + ‘]’
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
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.