Very Computer

Here is my situation with pubs and northwind databases
used for an example:

databases:
northwind
northwind_b
pubs
pubs_b

There is a view, trigger, and stored proc in pubs that
references the database northwind by means of the code
reading northwind.dbo.table.column

I Backup pubs
I Restore pubs to a new database called pubs_B

The view, trigger, and stored proc now need to point to
northwind_b so the code needs to be
changed to northwind_b.dbo.table.column

Is there an easy way to do this for a whole bunch of code?
I can do it manually but it takes forever.

Thanks.

The text of views,triggers and stored procedures are stored in ctext column
of syscomments table.Since updating system tables are not advisable this
method is undocumented .

From enterprise manager ( right click on the database |All tasks|Genarte sql
script) you can generate the scripts for all views,triggers and stored
procedures.Save it as a text file .Open it in a text editor and do a replace
all on 'northwind.dbo' to 'northwind_b.dbo'.Execute the modified script
against the database.

Dinesh, SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com

Quote:> Does anyone know of a tool that will search for a word and
> replace it in views, triggers, and stored procs???

> Here is my situation with pubs and northwind databases
> used for an example:

> databases:
> northwind
> northwind_b
> pubs
> pubs_b

> There is a view, trigger, and stored proc in pubs that
> references the database northwind by means of the code
> reading northwind.dbo.table.column

> I Backup pubs
> I Restore pubs to a new database called pubs_B

> The view, trigger, and stored proc now need to point to
> northwind_b so the code needs to be
> changed to northwind_b.dbo.table.column

> Is there an easy way to do this for a whole bunch of code?
> I can do it manually but it takes forever.

> Thanks.

-Sue

Quote:>Does anyone know of a tool that will search for a word and
>replace it in views, triggers, and stored procs???

>Here is my situation with pubs and northwind databases
>used for an example:

>databases:
>northwind
>northwind_b
>pubs
>pubs_b

>There is a view, trigger, and stored proc in pubs that
>references the database northwind by means of the code
>reading northwind.dbo.table.column

>I Backup pubs
>I Restore pubs to a new database called pubs_B

>The view, trigger, and stored proc now need to point to
>northwind_b so the code needs to be
>changed to northwind_b.dbo.table.column

>Is there an easy way to do this for a whole bunch of code?
>I can do it manually but it takes forever.

>Thanks.