Content search and replace

The development team I currently work in like to have content in line with production content but it very quickly becomes outdated.

To aid with this we have Go pipelines which restore production database backups over lower internal environment databases.

The CMS Site has many links to various other product journeys. These journeys have environment specific Urls. If we are are in our regression environment, we would like content links to hand off to the relevant regression product journeys.

Our go pipeline restores the production database and performs many content replacements by executing a search and replace stored procedure. Below you will find the stored procedure definition to search and replace content.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_CustomSearchAndReplace]') AND type in (N'P', N'PC'))
      DROP PROCEDURE [dbo].[usp_CustomSearchAndReplace]
GO

CREATE PROCEDURE [dbo].[usp_CustomSearchAndReplace]
      @SearchFor nvarchar(1000),
      @ReplaceWith nvarchar(1000) = ''
AS
      SELECT * FROM [tblContentProperty] WHERE [String] LIKE '%' + @SearchFor + '%' OR [LongString] LIKE '%' + @SearchFor + '%'
      SELECT * FROM [tblWorkContentProperty] WHERE [String] LIKE '%' + @SearchFor + '%' OR [LongString] LIKE '%' + @SearchFor + '%'

      IF LEN(LTRIM(RTRIM(@ReplaceWith))) > 0
      BEGIN
            PRINT 'Updating published pages'
            UPDATE [tblContentProperty] SET [String] = REPLACE([String], @SearchFor, @ReplaceWith) WHERE [String] LIKE '%' + @SearchFor + '%'
            UPDATE [tblContentProperty] SET [LongString] = REPLACE(CAST([LongString] AS nvarchar(max)), @SearchFor, @ReplaceWith) WHERE [LongString] LIKE '%' + @SearchFor + '%'
            PRINT 'Updating unpublished pages'
            UPDATE [tblWorkContentProperty] SET [String] = REPLACE([String], @SearchFor, @ReplaceWith) WHERE [String] LIKE '%' + @SearchFor + '%'
            UPDATE [tblWorkContentProperty] SET [LongString] = REPLACE(CAST([LongString] AS nvarchar(max)), @SearchFor, @ReplaceWith) WHERE [LongString] LIKE '%' + @SearchFor + '%'
            PRINT 'Updating soft links'
            UPDATE [tblContentSoftlink] SET [LinkURL] = REPLACE([LinkURL], @SearchFor, @ReplaceWith) WHERE [LinkURL] LIKE '%' + @SearchFor + '%'
      END