sql 2008 changing stored procedures

Posted on 2012-08-27
Last Modified: 2012-09-13
Hello we have copied a production databases over to our Crash & Burn environment for further testing, what I want do ois repoint stored procedures against each of the database, the command I was running is a as follows, this was run against all non-system databases

set @searchString = 'ProdServer'
select @text = (replace (@text,@searchString,'CrashBurn Server'))

I have also tried to run this command as well against non-system databases ie master, msdb.

insert into tblObjects(ObjectID, ObjectName,colid )
select, object_name(,c.colid
from syscomments as C
join sysobjects as O on =
      and o.type = 'P'
where C.text like '%'+ 'CrashandBurnServer' +'%' -- Modify this statement to suit your needs.

select * from syscomments
where id = 1045578763

has anyone had these issues before, we need to have this down pat for our DR testing as well.
I would be grateful for some sound advice
Question by:Amanda Walshaw
    LVL 142

    Accepted Solution

    insert into tbl .-...

    would not work to "change" stuff, but to insert stuff.

    however, for system tables, you cannot do it.
    you can only do alter procedures and alter views to modify the source code.
    for that, you have to read in the source code of the objects, and then indeed recompile it with the new text.

    Author Comment

    by:Amanda Walshaw
    I scripted the databases instead and that worked

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now