find and replace text in sproc

  • I need to find all sprocs having a misspelled word and dynamically correct.

    Googled and found a solution here

    Testing the non dynamic portion of it I'm getting error

    (1 row affected)

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near '.'.

    CODE finds, drops, creates new CREATE Procedure

    use test

    go

    Declare @spname nvarchar(max)

    Declare @moddef nvarchar(max)

    SELECT

    Replace ((REPLACE(definition,'subscriptonguid','subscriptionguid')),'ALTER','create')

    FROM sys.sql_modules a

    JOIN

    (   select type, name,object_id

    from sys.objects b

    where type in (

    'p' -- procedures

    )

    and is_ms_shipped = 0

    )b

    ON a.object_id=b.object_id where b.name = 'HK'

    exec('drop procedure dbo.' + @spname)

    execute sp_executesql @moddefSPROC which has misspelled column "subscriptonguid"

     

    Sample SPROC having misspelled column

    use test

    go

    CREATE procedure [dbo].[HK]

    as

    begin

    select top 10 subscriptonguid --missing the i in 'tion'

    from test.dbo.Subscription

    end

    go

    CREATE TABLE (misspelling is fixed)

    USE [test]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[Subscription](

    [SubscriptionGUID] [varchar](255) NULL

    ) ON [PRIMARY]

    GO

    In real life as well I have a misspelled column name on a table that I want to correct: subscriptonguid ->subscriptionguid and then fix referencing sprocs

    • This topic was modified 1 year, 2 months ago by  polkadot.
    • This topic was modified 1 year, 2 months ago by  polkadot.
    • This topic was modified 1 year, 2 months ago by  polkadot.

    --Quote me

  • you can't blindly replace all "create" by an "alter".

    Replace ((REPLACE(definition,'subscriptonguid','subscriptionguid')),'ALTER','create')

    you need to replace the above bit of code with

    • find procedure name position - split code in 2 blocks - before position (A) and after position (B)
    • replace the CREATE by ALTER on block A
    • Join block A and B for final code

    above will fail if you are one of those that puts comments BEFORE the create procedure command - not much you can do about those other than manually change them (and take the opportunity to move the code to AFTER the create procedure)

    but all this would be better done if you use Visual Studio.

    • create a database project with your database code
    • Identify scripts requiring changes by using a search tool like Agent Ransack
    • make the code changes using notepad++ - can search for the offending string and replace in ALL documents
    • then using VS Schema Compare generate alter scripts

     

     

     

  • The idea of doing a DROP & CREATE fills me with dread - all the bespoke permissions you could lose...

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • Thomas Rushton wrote:

    The idea of doing a DROP & CREATE fills me with dread - all the bespoke permissions you could lose...

    Ditto that.  Since this is a 2019 thread, I'm thinking that CREATE OR ALTER would be much safer.  Haven't read the rest of this thread to know for sure, though.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • polkadot wrote:

    I need to find all sprocs having a misspelled word and dynamically correct.

    Why don't you have your stored procedure code in a source control system?

  • +1

  • Noo source control, also there are hundreds of sprocs containing this error.

    I provided DDL. Can anyone show me dynamic cursor way?

     

    • This reply was modified 1 year, 2 months ago by  polkadot.
    • This reply was modified 1 year, 2 months ago by  polkadot.

    --Quote me

  • Install this stored procedure

    IF Object_Id(N'INFGenerateObjectScript', N'P') IS NULL
    BEGIN
    EXEC ('CREATE PROCEDURE [dbo].[INFGenerateObjectScript] AS BEGIN SELECT 1 END')
    END
    GO

    GO
    -- **********************************************************************
    -- PROCEDURE INFGenerateObjectScript
    -- Description: Generates the create script for all stored
    -- procedures on a database.
    -- The results are output to the messages portion of
    -- the SQL server Management Studio screen
    -- $Revision: 1.0 $
    -- Usage:
    -- EXEC INFGenerateObjectScript '%', 1, 1, 1
    -- EXEC INFGenerateObjectScript 'INFGenerateObjectScript', 1, 1
    -- **********************************************************************
    ALTER PROCEDURE [dbo].INFGenerateObjectScript
    (
    @ProcName varchar(128) = '%',
    @IncludeAuditTrail bit = 1,
    @IncludeUseDatabase bit = 1,
    @IncludeSeperators bit = 0,
    @QuoteText bit = 0, -- Quotes the ALTER Script and executes string
    @AuthorName varchar(50) = NULL -- Optional if left will output the current user.
    )
    AS
    BEGIN

    SET NOCOUNT ON

    DECLARE @myCursor CURSOR
    DECLARE @ProcedureName varchar(200)
    DECLARE @ProcedureId int
    DECLARE @RoutineSchema varchar(128)
    DECLARE @RoutineTypeName varchar(128)
    DECLARE @RoutineType varchar(2)

    DECLARE @myCursor2 CURSOR
    DECLARE @PropertyName varchar(128)
    DECLARE @PropertyValue varchar(8000)

    DECLARE @CurrentDB varchar(100)
    SELECT @CurrentDB = DB_Name()

    DECLARE @StoredProcs TABLE
    (
    ProcedureName varchar(100),
    ProcedureId int,
    RoutineSchema varchar(128),
    RoutineTypeName varchar(128),
    RoutineType varchar(2)
    )

    DECLARE @myCursor3 CURSOR
    DECLARE @ProcedureLine nvarchar(255)
    DECLARE @prevProcedureLine nvarchar(280) -- Bigger than @ProcedureLine to allow for extra quotes
    SET @prevProcedureLine = '' --Initialise
    DECLARE @Quote AS varchar(2)
    DECLARE @DoubleQuote AS varchar(2)
    SET @Quote = ''''
    SET @DoubleQuote = @Quote + @Quote
    DECLARE @ReplaceCreateWithAlter bit

    DECLARE @StoredProcsDefinitionTable TABLE
    (
    ProcedureLine nvarchar(255)
    )

    IF @AuthorName IS NULL
    SET @AuthorName = RIGHT(SUser_SName(), Len(SUser_SName()) - CharIndex('\', SUser_SName()))
    --END IF

    INSERT INTO @StoredProcs
    (
    ProcedureName,
    ProcedureId,
    RoutineSchema,
    RoutineTypeName,
    RoutineType
    )
    SELECT O.[Name],
    O.Id,
    IsNull(R.ROUTINE_SCHEMA, 'dbo'),
    IsNull(R.ROUTINE_TYPE, CASE
    WHEN O.[TYPE] = 'TR' THEN 'TRIGGER'
    ELSE NULL --(SELECT O1.[TYPE_DESC] FROM sys.objects O1 WHERE O1.[Object_Id] = O.[Id])
    END),
    O.[Type]
    FROM SysObjects O
    LEFT JOIN INFORMATION_SCHEMA.ROUTINES R
    ON R.ROUTINE_NAME = O.[Name]
    WHERE O.category = 0
    AND O.[name] NOT LIKE 'sp\_' ESCAPE '\'
    AND O.[name] LIKE @ProcName
    AND IsNull(R.ROUTINE_TYPE, CASE
    WHEN O.[TYPE] = 'TR' THEN 'TRIGGER'
    ELSE NULL --(SELECT O1.[TYPE_DESC] FROM sys.objects O1 WHERE O1.[Object_Id] = O.[Id])
    END) IS NOT NULL
    ORDER BY R.ROUTINE_SCHEMA ASC,
    R.ROUTINE_TYPE ASC,
    O.[Type] ASC,
    O.[name] ASC


    SET @myCursor = CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY
    FOR SELECT ProcedureName,
    ProcedureId,
    RoutineSchema,
    RoutineTypeName,
    RoutineType
    FROM @StoredProcs
    ORDER BY RoutineSchema ASC,
    RoutineTypeName ASC,
    RoutineType ASC,
    ProcedureName ASC

    OPEN @myCursor

    FETCH NEXT
    FROM @myCursor
    INTO @ProcedureName,
    @ProcedureId,
    @RoutineSchema,
    @RoutineTypeName,
    @RoutineType


    WHILE (@@Fetch_Status = 0)
    BEGIN

    IF (@IncludeSeperators = 1)
    BEGIN
    PRINT '-- %%START%% ' + @RoutineTypeName + '.' + @RoutineSchema + '.' + @ProcedureName
    END
    --END IF

    IF (@IncludeAuditTrail = 1)
    BEGIN
    PRINT '-- **********************************************************************'
    PRINT '-- $Workfi' + 'le:$'
    PRINT '-- AUTHOR : ' + @AuthorName
    PRINT '-- DATE CREATED : ' + Convert(varchar, GetDate(), 103)
    PRINT '-- REVISION : $Revi' + 'sion:$'
    PRINT '-- DESCRIPTION : Script to create if non existent then amend SQL Server'
    PRINT '-- ' + Lower(@RoutineTypeName) + ' ' + @ProcedureName
    PRINT '-- **********************************************************************'
    PRINT '-- $Lo' + 'g:$'
    PRINT '--'
    PRINT '--'
    IF @IncludeUseDatabase = 1
    BEGIN
    PRINT 'USE ' + @CurrentDB
    END
    --END IF
    PRINT 'GO'
    PRINT ''
    END
    --END IF

    IF @RoutineType IN('TR') -- Trigger Processing just drop the trigger then create it rather the altering trigger
    BEGIN
    SET @ReplaceCreateWithAlter = 0 -- False
    PRINT 'IF Object_Id(N'+ @Quote + @ProcedureName + @Quote + ', N' + @Quote + RTrim(@RoutineType) + @Quote + ') IS NOT NULL'
    PRINT 'BEGIN'
    PRINT ' DROP ' + @RoutineTypeName + ' [' + @RoutineSchema + '].[' + @ProcedureName + ']'
    PRINT 'END'
    END
    ELSE
    BEGIN
    SET @ReplaceCreateWithAlter = 1 -- True
    PRINT 'IF Object_Id(N'+ @Quote + @ProcedureName + @Quote + ', N' + @Quote + RTrim(@RoutineType) + @Quote + ') IS NULL'
    PRINT 'BEGIN'
    IF @RoutineType = 'P'
    PRINT ' EXEC (''CREATE PROCEDURE [' + @RoutineSchema + '].[' + @ProcedureName + '] AS BEGIN SELECT 1 END'')'
    ELSE IF @RoutineType = 'TF'
    PRINT ' EXEC (''CREATE FUNCTION [' + @RoutineSchema + '].[' + @ProcedureName + '] () RETURNS @table TABLE (VALUE nchar(1)) AS BEGIN RETURN END'')'
    ELSE IF @RoutineType = 'FN'
    PRINT ' EXEC (''CREATE FUNCTION [' + @RoutineSchema + '].[' + @ProcedureName + '] () RETURNS varchar(1) AS BEGIN RETURN ''''X'''' END'')'
    --END IF

    SET @myCursor2 = CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY
    FOR SELECT ep.[Name],
    Cast(ep.[value] AS varchar(8000))
    FROM sys.extended_properties ep
    WHERE ep.major_id = @ProcedureId
    AND ep.[Name] NOT LIKE 'MS/_%' ESCAPE '/'

    OPEN @myCursor2
    FETCH NEXT
    FROM @myCursor2
    INTO @PropertyName,
    @PropertyValue

    WHILE (@@Fetch_Status = 0)
    BEGIN
    PRINT ' EXEC sys.sp_addextendedproperty @name=N'+ @Quote + @PropertyName + @Quote + ', @value=N' + @Quote + @PropertyValue + @Quote + ' ,@level0type=N''SCHEMA'', @level0name=N'''
    + @RoutineSchema + @Quote + ', @level1type=N' + @Quote + @RoutineTypeName + @Quote + ', @level1name=N' + @Quote + @ProcedureName + @Quote
    FETCH NEXT
    FROM @myCursor2
    INTO @PropertyName,
    @PropertyValue
    END
    --END WHILE
    CLOSE @myCursor2
    DEALLOCATE @myCursor2

    PRINT 'END'
    END
    --END IF

    PRINT 'GO '
    PRINT ''
    PRINT 'GO '

    INSERT INTO @StoredProcsDefinitionTable
    (
    ProcedureLine
    )
    EXEC sp_helptext @ProcedureName

    SET @myCursor3 = CURSOR FAST_FORWARD
    FOR
    SELECT ProcedureLine
    FROM @StoredProcsDefinitionTable

    OPEN @myCursor3

    FETCH NEXT
    FROM @myCursor3
    INTO @ProcedureLine

    DECLARE @CreateFound bit
    DECLARE @FirstLinePrinted bit

    SET @CreateFound = 0 --False
    SET @FirstLinePrinted = 0 --Initialise

    WHILE (@@Fetch_Status = 0)
    BEGIN
    IF (@CreateFound = 0)
    AND (PatIndex ('%CREATE%' + @RoutineTypeName + '%' + @ProcedureName + '%', @ProcedureLine) BETWEEN 1 AND 4)
    BEGIN
    SET @ProcedureLine = Replace(@ProcedureLine, char(9), ' ')
    SET @ProcedureLine = Replace(@ProcedureLine, ' ', ' ')
    SET @ProcedureLine = Replace(@ProcedureLine, ' ', ' ')
    SET @CreateFound = 1
    IF @QuoteText = 0
    BEGIN
    IF @ReplaceCreateWithAlter = 1
    BEGIN
    PRINT Replace(@ProcedureLine, 'CREATE ' + @RoutineTypeName, 'ALTER ' + @RoutineTypeName)
    END
    ELSE
    BEGIN
    PRINT @ProcedureLine
    END
    --END IF
    END
    ELSE
    BEGIN
    IF @FirstLinePrinted = 0
    BEGIN
    PRINT 'DECLARE @sSQL varchar(Max)'
    PRINT 'SET @sSQL = ' + @Quote + Replace(@ProcedureLine, 'CREATE ' + @RoutineTypeName, 'ALTER ' + @RoutineTypeName)
    SET @FirstLinePrinted = 1
    END
    ELSE
    BEGIN
    IF @ReplaceCreateWithAlter = 1
    BEGIN
    PRINT Replace(Replace(@ProcedureLine, 'CREATE ' + @RoutineTypeName, 'ALTER ' + @RoutineTypeName), @Quote, @DoubleQuote)
    END
    ELSE
    BEGIN
    PRINT Replace(@ProcedureLine, @Quote, @DoubleQuote)
    END
    --END IF
    END
    --END IF
    END
    --END IF
    END
    ELSE
    IF @QuoteText = 0
    BEGIN
    PRINT @ProcedureLine
    END
    ELSE
    BEGIN
    IF @FirstLinePrinted = 0
    BEGIN
    PRINT 'DECLARE @sSQL varchar(Max)'
    PRINT 'SET @sSQL = ''' + Replace(@ProcedureLine, @Quote, @DoubleQuote)
    SET @FirstLinePrinted = 1
    END
    ELSE
    SET @prevProcedureLine = Replace(@ProcedureLine, @Quote, @DoubleQuote)
    --END IF
    END
    --END IF
    --END IF

    FETCH NEXT
    FROM @myCursor3
    INTO @ProcedureLine

    IF @prevProcedureLine <> ''
    BEGIN
    IF (@@Fetch_Status <> 0) AND (@QuoteText = 1)
    PRINT @prevProcedureLine + @Quote -- Last line
    ELSE
    PRINT @prevProcedureLine -- Last line
    --END IF
    SET @prevProcedureLine = '' --Initialise
    END
    --END IF
    END
    --END WHILE

    DELETE @StoredProcsDefinitionTable
    CLOSE @myCursor3
    DEALLOCATE @myCursor3
    IF (@FirstLinePrinted > 0) AND (@QuoteText = 1)
    PRINT 'EXEC (@sSQL)'
    PRINT 'GO '
    PRINT ''
    PRINT ''

    IF (@IncludeSeperators = 1)
    BEGIN
    PRINT '-- %%END%% ' + @RoutineTypeName + '.' + @RoutineSchema + '.' + @ProcedureName
    END
    --END IF

    FETCH NEXT
    FROM @myCursor
    INTO @ProcedureName,
    @ProcedureId,
    @RoutineSchema,
    @RoutineTypeName,
    @RoutineType

    END
    --END WHILE

    CLOSE @myCursor
    DEALLOCATE @myCursor

    END
    GO

    Then run it. It will generate a script for each stored procedure in your database, just copy and paste the messages into SSMS and you have a script that will alter all the stored procedures. If you do a find and replace on the script you can change it so the code has the replacements you want. Then run it to update the sprocs.

    If you then read this article it explains how you can save the script with all the SPs to a file, then run a vbscript on it to chop it into individual files, one for each stored procedure. Then you can add them to source control and have proper version control on your code.

     

  • why don't you do what I said and install Visual Studio (SSDT) - you can then generate a VS solution/project with all objects on your database, do the global replace and reapply the changes to your db in one go.

    No need for a license of VS - SSDT is free

    download from here https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-ver15

    or here

    https://docs.microsoft.com/en-us/sql/ssdt/previous-releases-of-sql-server-data-tools-ssdt-and-ssdt-bi?view=sql-server-ver15

     

    you should have this in any case for all your db's

  • It's not like I can just install Visual Studio on the server that is hosting the databases without discussing with higher ups.  Additionally I believe this requires a coding ability outside of SQL.

    I'm looking for a sql solution. Can anyone on sqlservercentral help?

    • This reply was modified 1 year, 2 months ago by  polkadot.

    --Quote me

  • polkadot wrote:

    It's not like I can just install Visual Studio on the server that is hosting the databases without discussing with higher ups.  Additionally I believe this requires a coding ability outside of SQL.

    I'm looking for a sql solution. Can anyone on sqlservercentral help?

    first you would not install VS on the server - it is a client application.

    second - as pointed out you should have all your code on some type of source control system - so do the changes there and then deploy the changed code to your servers the same way as you would do with any other code change your company does.

    third - it does not require any coding abilities - it is pure click all the way (apart from the global replace where you do need to type)

    as for t-sql changes - I've pointed out some issues so if you wish to have a pure T-SQL option you can take what I mentioned in consideration and improve your code so it does what is required .

    Note that you need to do the table changes prior to do the code changes or creating the proc will fail.

     

    and if you need further help put here (following the correct way of putting code on the forums) a fully functional version of your replacement code - including the dynamic sql required to loop through your databases (which you can find some examples here on the forum or google)

  • The fastest way for you to do this is in SSMS Object Explorer, select the "Stored Procedures" node, then press F7. In the right-hand pane then select all the stored procedures and right-click "Script Stored Procedure\Create To\New Query Editor Window". This will script all the stored procedures into a single script.

    In the script then do a find "CREATE PROCEDURE " and replace with "ALTER PROCEDURE "

    Then do a find and replace to correct the error you want to, then execute the script to apply the changes.

  • Thank you Jonathan AC Roberts for two SQL ways.  I currently have no idea what the CURSOR that you shared does, but I'm looking it over and I hope it will be a good framework that get's me going. Jonathan, did you personally write this cursor script that you shared?  What does setting variable to '%' do ie. @ProcName                   varchar(128) = '%' ??

    I like the simplicity and 'accessibility' of the SSMS directions that I marked as 'ANSWER' .

    As for the other ways suggested (VS) it's outside of where I wanted to do my solutioning, but intriguing. Bookmarked for review.

    • This reply was modified 1 year, 2 months ago by  polkadot.
    • This reply was modified 1 year, 2 months ago by  polkadot.
    • This reply was modified 1 year, 2 months ago by  polkadot.
    • This reply was modified 1 year, 2 months ago by  polkadot.
    • This reply was modified 1 year, 2 months ago by  polkadot.
    • This reply was modified 1 year, 2 months ago by  polkadot.
    • This reply was modified 1 year, 2 months ago by  polkadot.
    • This reply was modified 1 year, 2 months ago by  polkadot.
    • This reply was modified 1 year, 2 months ago by  polkadot.
    • This reply was modified 1 year, 2 months ago by  polkadot.
    • This reply was modified 1 year, 2 months ago by  polkadot.
    • This reply was modified 1 year, 2 months ago by  polkadot.
    • This reply was modified 1 year, 2 months ago by  polkadot.
    • This reply was modified 1 year, 2 months ago by  polkadot.

    --Quote me

  • Something simple like below might work. I've had issues with one of the tables, not sure if it was sql_modules... but it truncates the procedure in the sys view.

    *Wrote on my phone, might have to covert(nvarchar(max),) for object definition.

     

     

    Declare @processCode nvarchar(max)

    Declare @phraseToReplace ncarchar(100)

    Declare @phraseToReplace ncarchar(100)

    Declare ProcessFix Cursor Local Fast_forward

    For

    Select replace(OBJECT_DEFINITION(object_id),@phraseToReplace,@newPhrase)

    From sys.procedures

    Where charindex(@phraseToReplace, OBJECT_DEFINITION(object_id))>0

     

    Open ProcessFix

    Fetch next from ProcessFix into @ProcessCode

    While @@fetchstatus=0

    Begin

     

    Select @processcode=replace(@processcode,'create procedure','alter procedure')

    Exec sp_executesql @processcode

    Fetch next from ProcessFix into @ProcessCode

    End

    Close processfix

    Deallocate processfix

     

     

    Here's something similar I did some months ago

    https://youtu.be/1NGcSbZtq9c

    • This reply was modified 1 year, 2 months ago by  elric.sims. Reason: reread code, removed a closing paren

    MCSA, Data Architect
    SQL Master Data Management

    https://youtube.com/elricsims-dataarchitect

    9001st Normal Form

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply