[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2008-02-11
3
Medium Priority
?
1,455 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:JCM0165
  • 2
3 Comments
 
LVL 23

Expert Comment

by:debuggerau
ID: 20871795
sp_changeobjectowner 'dbname.dboOwner.object name', 'dbo'

Where object name is the name of the table, stored procedure etc...
0
 

Author Comment

by:JCM0165
ID: 20871853
I changed the object owners already. Now I need to change their references within stored procedures and functions to reflect that change.
0
 

Accepted Solution

by:
JCM0165 earned 0 total points
ID: 20914305
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

590 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