SQL Server – How to Change FROM Clause of Multiple Views at Once

sql server

I have multiple views that are the sources for a collection of scripts I use for loading data from NAV into our DWH, meaning the view collects fields from multiple tables in our NAV database and the loading script just has the view as its source.
The problem with this is that the client identifier on the NAV database is changing every couple of months.

As I obviously don't want to go into every single view and update the source table with the new client identifier, my question is, whether or not it is possible to change the source table of multiple views at once. I am using MS SQL Server 13 btw.

Best Answer

I am not sure if I fully understood your question however if it is about changing source table of selected views or all views, you can do the same using SSMS(SQL Server Management Studio).

You can generate scripts from database as below:

Generate Scripts

In the screen, either you can select all view or select the one that you need as below:

Select DB Object

You can save them to a new query window:

New Query Window

Alternatively you can save this as a SQL file. Now, using simple text editor, you can do replace all from existing table name to new table name and either you can run them directly in the new query window or can execute them from SQL file. This file can be handy whenever there is change in table name, you can just find and replace table and would work for you.

One great point made by Aaron Bertrand, if you create another view(only one) wherein you just include the definition as :

create view NotChangingName
as
Select * from changingName

You don't need to change each view, just one change will be sufficient.

Hope this helps.

Related Question