Slightly OT: SQL Server mass update of views

Yahoo Archive
1
This one looks promising:
http://stackoverflow.com/questions/2983857/how-to-do-bulk-update-of-views

SELECT
REPLACE (REPLACE (sm.definition, 'CREATE VIEW', 'ALTER VIEW'), 'DB1.', 'DB2.') + '
GO'
FROM
sys.sql_modules sm
JOIN
sys.objects o ON sm.object_id = o.object_id
WHERE
sm.definition LIKE '%DB1.%' AND o.type = 'V'


[cid:2a7969.png@46e080b9.4494a3db]


Joe Rojas
Director of Information Technology
(781) 408-9278 Mobile
(781) 573-0291 Local
(781) 232-5191 Fax


[http://matsinc.com/images/e-mail-signatures/tagline.jpg%5d
[http://matsinc.com/images/e-mail-signatures/graphics/logo_matsinc.png%5d<http://matsinc.com> [http://matsinc.com/images/e-mail-signatures/graphics/icon_twitter.png%5d <http://twitter.com/Mats_Inc> [http://matsinc.com/images/e-mail-signatures/graphics/icon_facebook.png%5d <http://www.facebook.com/commercial.flooring> [http://matsinc.com/images/e-mail-signatures/graphics/icon_youtube.png%5d <http://www.youtube.com/user/MatsIncTV> [http://matsinc.com/images/e-mail-signatures/graphics/icon_pinterest.png%5d <http://www.pinterest.com/contractfloors/> [http://matsinc.com/images/e-mail-signatures/graphics/icon_blog.png%5d <http://matsinc.com/blog>

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Saturday, October 17, 2015 1:30 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Slightly OT: SQL Server mass update of views



Will be interested if you find something.
In past moves I've had to update views manually.

Another move was postponed (thankfully) & I haven't thought about this since.
I had been trying a linked server named to trick the old views/reports, just to get by in the short term.

I found a couple of posts too but did not test anything..


How to do bulk update of views?<http://stackoverflow.com/questions/2983857/how-to-do-bulk-update-of-views>


[Image removed by sender. image]<http://stackoverflow.com/questions/2983857/how-to-do-bulk-update-of-views>

How to do bulk update of views? <http://stackoverflow.com/questions/2983857/how-to-do-bulk-update-of-views>
My database has about 30 views, most of which have a reference to another database on this server (call it DB1). Now, without going into the reasons why, I ne...

View on stackoverflow.com <http://stackoverflow.com/questions/2983857/how-to-do-bulk-update-of-views>

Preview by Yahoo





Mass editing of SQL Server objects - Tomas Lind<http://tomaslind.net/2014/12/10/mass-editing-sql-server-objects/>


[Image removed by sender. image]<http://tomaslind.net/2014/12/10/mass-editing-sql-server-objects/>

Mass editing of SQL Server objects - Tomas Lind <http://tomaslind.net/2014/12/10/mass-editing-sql-server-objects/>
A method for mass editing SQL Server objects.

View on tomaslind.net <http://tomaslind.net/2014/12/10/mass-editing-sql-server-objects/>

Preview by Yahoo








[Non-text portions of this message have been removed]
2
Hi All,

We replicate our E9 Progress DB to SQL Server, using Epicor’s replication module, so that we have a separate server for reporting.
That SQL replicated DB is called Epicor905.
To keep all of our views for reporting separate, I created another DB on the same SQL Server called Epicor905Reporting.
All of the view in Epicor905Reporting use the tables in Epicor905.

As we move to ERP10, I will be querying directly off of the ERP10 DB as opposed to replicating to another DB. That’s the plan at least for now.

I would like to back up the Epicor905Reporting DB and restore it on the ERP10 SQL Server.

My question is, is there some way to mass update all of the views in Epicor905Reporting to point to the new ERP10 DB?
For example, a view may reference Epicor905.dbo.OrderHed and now it will need to reference ERP10DB.[dbo or Erp].OrderHed.

I do understand that there are some additional tweaks that would have to be made to the views because of schema changes but from most of what I have seen in ERP10, the changes are not drastic.

[cid:ac31fb.png@b576bd76.469744d3]


Joe Rojas
Director of Information Technology
(781) 408-9278 Mobile
(781) 573-0291 Local
(781) 232-5191 Fax


[http://matsinc.com/images/e-mail-signatures/tagline.jpg%5d
[http://matsinc.com/images/e-mail-signatures/graphics/logo_matsinc.png%5d<http://matsinc.com> [http://matsinc.com/images/e-mail-signatures/graphics/icon_twitter.png%5d <http://twitter.com/Mats_Inc> [http://matsinc.com/images/e-mail-signatures/graphics/icon_facebook.png%5d <http://www.facebook.com/commercial.flooring> [http://matsinc.com/images/e-mail-signatures/graphics/icon_youtube.png%5d <http://www.youtube.com/user/MatsIncTV> [http://matsinc.com/images/e-mail-signatures/graphics/icon_pinterest.png%5d <http://www.pinterest.com/contractfloors/> [http://matsinc.com/images/e-mail-signatures/graphics/icon_blog.png%5d <http://matsinc.com/blog>



[Non-text portions of this message have been removed]
3

Will be interested if you find something.
In past moves I've had to update views manually.

Another move was postponed (thankfully) & I haven't thought about this since.
I had been trying a linked server named to trick the old views/reports, just to get by in the short term.

I found a couple of posts too but did not test anything..


How to do bulk update of views?
How to do bulk update of views?
My database has about 30 views, most of which have a reference to another database on this server (call it DB1). Now, without going into the reasons why, I ne...
Preview by Yahoo


Mass editing of SQL Server objects - Tomas Lind
Mass editing of SQL Server objects - Tomas Lind
A method for mass editing SQL Server objects.
Preview by Yahoo