Need to search and replace text in all stored procedures in sql 2005 db

I am moving my sql database from one server to another at a different hosting company.
In order to connect, I had to change the owner of the tables, functions and stored procedures from
oldowner.stored_procedure_1
to
dbo.stored_procedure_1
etc.

The problem is there are many references in stores procedures to tables with the oldowner name.
So, I want to do a search and replace.
for example, to change
SELECT Names from OldOwner.Dogs
to
SELECT Names from DBO.Dogs

Is there a simple way to do this?

Thanks in advance
JCM0165Asked:
Who is Participating?
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.

debuggerauCommented:
sp_changeobjectowner 'dbname.dboOwner.object name', 'dbo'

Where object name is the name of the table, stored procedure etc...
0
JCM0165Author Commented:
I changed the object owners already. Now I need to change their references within stored procedures and functions to reflect that change.
0
JCM0165Author Commented:
I figured it out myself. Here's how:

Here is what worked. First run this stored procedure for database obejects that need the owner changed to dbo. I did it for tables, stored procedures, and inline functions by changing the "type = " line to represent each


USE [FastFit]
GO
/****** Object: StoredProcedure [dbo].[AAA_CHANGE_SP_OWNER_TO_DBO] Script Date: 02/16/2008 06:19:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER Procedure [dbo].[AAA_CHANGE_SP_OWNER_TO_DBO]
AS
DECLARE
@OldOwner sysname,
@NewOwner sysname

SET @OldOwner = 'fasterfitness'
SET @NewOwner = 'dbo'

DECLARE CURS CURSOR FOR
SELECT
name
FROM
sysobjects
WHERE
type = 'p'
AND
uid = (SELECT uid FROM sysusers WHERE name = @OldOwner)
AND
NOT name LIKE 'dt%' FOR READ ONLY

DECLARE @ProcName sysname

OPEN CURS

FETCH CURS INTO @ProcName

WHILE @@FETCH_STATUS = 0
BEGIN
IF @@VERSION >= 'Microsoft SQL Server 2005'
BEGIN
EXEC('alter schema ' + @NewOwner + ' transfer ' + @OldOwner + '.' + @ProcName)
exec('alter authorization on ' + @NewOwner + '.' + @ProcName + ' to schema owner')
END
ELSE
EXEC('sp_changeobjectowner ''' + @OldOwner + '.' + @ProcName + ''', ''' + @NewOwner + '''')

FETCH CURS INTO @ProcName
END

CLOSE CURS
DEALLOCATE CURS


Then, to change the references to the old owner names within the stored procedures and functions,



In SQL Server mgmt Studio
Right mouse on db, click tasks> Generate Scripts
Next
Select db
Select Stored Procedures and/or User-defined functions
next
Select All
Next
Script to New Query Window
Next
Finish

Close

In query generated-

find/replace fasterfitness. with dbo.
find/replace "Create Function (or Stored Procedure)"
with "Alter Function (or Stored Procedure)"

Click Execute
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
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
Microsoft SQL Server 2005

From novice to tech pro — start learning today.