1

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.

  • 1
    So the client identifier is hard-coded into all of these views? Why don't you put the identifier in its own view that all the other views reference? Then you only have to change it in one place. – Aaron Bertrand Jul 15 at 13:55
  • @AaronBertrand Added your point in my answer, I had done so much of change like the thing which is asked in question and used to follow the approach I mentioned in my answer, if I could have thought about your approach, would have saved lot of time and effort. nevertheless learning is always good. Thanks again. – Learning_DBAdmin Jul 18 at 5:26
2

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.

  • This is almost what I wanted to do. I actually already have the views so I would like to alter them. I suppose I could just drop them and create them using the script but if it were possible to alter them directly, I would prefer that. Replacing CREATE with ALTER in the script threw a syntax error. – toasty Jul 15 at 11:33
  • Please post the error that you are getting if you are replacing create with alter. Possibly you will have some extended property in the view that requires to be handled. – Learning_DBAdmin Jul 15 at 11:35
  • Nevermind actually. I just had to ditch the part of the script defining extended properties and so on. Works perfectly fine now, cheers! – toasty Jul 15 at 11:39
  • You can save this file where you have removed extended property and have replaced create with alter, this can be used next time onward, Thanks. – Learning_DBAdmin Jul 15 at 11:44
  • What do you mean by the client identifier? – Jhunter1 Jul 15 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.