Script changes to stored procedures

Hi,
I have around 40 stored procedures all of them need to be amended, I will need to amend the select statement , my question is how to script the changes I.e. how to automate the changes as the first amendment will be to point to test data then all of them need to be amended again to point to live data.

appreciate your help in advance!
UKITAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
If you could give the sample Stored procedure and the before and after how it should look like, it will help experts to suggest.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
UKITAuthor Commented:
Sure,

this is before
ALTER PROCEDURE [dbo].[XXXXX]
      -- Add the parameters for the stored procedure here
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT
        BB.MID
      , BB.CD
      , BB.Mf as OMf
      from CM
inner join dbo.ST
on cm.STID = BB.STID

left join dbo.TT
on cm.STTID = tt.TTID

AFTER

ALTER PROCEDURE [dbo].[XXXXX]
      -- Add the parameters for the stored procedure here
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT
        BB.MID
      , BB.CD
      , BB.Mf as OMf
======xxxx=====
,NMF as ANMF
=======xxxx=====
    from CM

=================This is the change that has to be scripted ==Here=
INNER JOIN
[server].[dbname].[dbo].[table]
on cm.[MRf]=[omc] collate database_default
=========================================================
inner join dbo.ST
on cm.STID = BB.STID

left join dbo.TT
on cm.STTID = tt.TTID

the changes are in between the ====== signs
0
Walter RitzelSenior Software EngineerCommented:
The change I' seeing highlighted is to add a field to the projection of the SELECT and a new inner join.
If you want to automate that, I suggest dynamic SQL statements. In that way, you change these 40 procedures once and will never worry about.
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Shaun KlineLead Software EngineerCommented:
I would suggest creating a view that points to the table on the foreign server. Modify all of your stored procedures to use this new view. Then when you are ready to switch to the production table, just modify the view to point to the correct table.
0
Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
I did not test this.
This is powershell code, You need to save the following code as file.ps1 and right click the file and click "run with Powershell". It will modify one stored procedure and then stops for you to verify and continue to next procedure.
In the first 2 lines, you enter the right server name and database name.

$inst = "YourServerName"
$database = "DatabaseName"


$srch = "OMf"
$repl = " OMf, NMF as ANMF " 

$src2 = "inner"
$repl2 = " INNER JOIN
[server].[dbname].[dbo].[table]
on cm.[MRf]=[omc] collate database_default
inner "

# Connect to the specified instance                              
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $inst                              
                               
# Get the specified database where the changes will be made                              
$db = $s.Databases[$database]                              
                               
$procs = $db.StoredProcedures                              
foreach ($proc in $procs) 
{                              
    if($proc.IsSystemObject -eq $False) 
        {                              
            $txt = $proc.TextBody                              
            $chgd = $False                              
                          
            # Check to see if the text of the proc contains the search string and replace it if so                              
            if ($txt.Contains($srch) -eq $True)
             {                              
                $txt = $txt.Replace($srch,$repl)                              
                $chgd = $True                              
             }
             if ($txt.Contains($srch2) -eq $True)
             {                              
                $txt = $txt.Replace($srch2,$repl2)                              
                $chgd = $True                              
             }                              
                               
            # If a change occurred, set the updated TextBody property and alter the proc                              
            if ($chgd = $True) 
             {                              
                $proc.TextBody = $txt                              
                $proc.Alter()                              
             }                              
        }                              
   Read-host "Please check the stored procedure and if you are happy Press Enter Key to continue. If not Press Control+C to stop"
   
}

Open in new window

0
Scott PletcherSenior DBACommented:
I think this code should be able to generate the new code for you.  Then copy it from the output window to a query window to run.  Or, if you need it to run automatically without checking it, let me know and we can add code for that.

--press Ctrl-T (text output) before running; press Ctrl-D to go back to grid output after running.
SET NOCOUNT ON
SELECT REPLACE(REPLACE(REPLACE(proc_code,
    'CREATE PROCEDURE', 'ALTER PROCEDURE'),
    ', BB.Mf as OMf', ', BB.Mf as OMf, NMF as ANMF'),
    'from CM', 'from CM INNER JOIN ' +
        '[server].[dbname].[dbo].[table] ' +
        'on cm.[MRf]=[omc] collate database_default ') + '
GO' + '
'
FROM sys.objects o
CROSS APPLY (
    SELECT OBJECT_DEFINITION(o.object_id) AS proc_code
) AS assign_alias_names
WHERE
    o.type = 'P' AND
    (proc_code LIKE '%, BB.Mf as OMf%' OR
     proc_code LIKE '%from CM[^#0-9a-z]%')
SET NOCOUNT OFF
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ukit, do you still need help with this question?
0
UKITAuthor Commented:
This solution was the best for my case, thank you so much for the swift help and sorry for the delay response, much appreciated guys
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Scripting Languages

From novice to tech pro — start learning today.