Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to Automatically "recompile" views

Posted on 2004-04-08
8
Medium Priority
?
1,154 Views
Last Modified: 2012-06-27
I have a SQL Server 2000 database with hundreds of views and stored procedures. Views are often nested several levels. As a very simple example I have a view called vw1 that looks like:

select tbl1.* from tbl1 inner join tbl2 on tbl1.colx = tbl2.colx

If I then add a new column to tbl1, this new column is not reflected in the vw1 view. To get the column to show up I have to open vw1 in design mode, uncheck the * in the diagram pane, and then recheck the * in the diagram pane. I have to do the same thing for every view that is base on this view if a select * is used. It seems strange to me that if a view only involves 1 table, e.g. select * from tbl1, the new column shows up automatically. With hundreds of views, it's all too easy to miss one when a new column is added to an underlying table. Is there some transact sql command or some other mechanism to automatically "recompile" all views?
0
Comment
Question by:rmk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 3

Expert Comment

by:jayrod
ID: 10783058
are there any indexes on this view? if so that would cause a problem with scema changes.

another route you could go would be to put schema-binding on each of the views. So that if any of the dependent schema changes it would force you to go change the view as well.

While the above isn't automatic it would be a good way to make sure changes are made when you want them to be
0
 
LVL 34

Expert Comment

by:arbert
ID: 10783248
you can use sp_refreshview, but this isn't exactly what you want....Views aren't compiled.....

Schemabinding just forces you to change the view--doesn't automatically do it for you.  Personally, I think it's a bad idea to have the system automatically change it for you.  

Evaluate your change, the impact it has on the system, and the dependencies.  If you have SQL Server adding columns in view for you, you might totally change query plans and not even be aware of it.
0
 

Author Comment

by:rmk
ID: 10783288
There are no indexes on the views.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 10784149
The script below may help.  It will do an sp_refreshView on *all* non-system views.  As you know, it is best to refresh a view if needed, otherwise you can abend or strange results.

Unfortunately, I had to comment out the code to check to limit to a certain base table name(s) because there are not always entries in information_schema.view_table_usage for multi-table views.


DECLARE @tables NVARCHAR(4000)
DECLARE @view SYSNAME
DECLARE @sql NVARCHAR(500)

SET @tables = NULL


SET @sql = N'EXEC sp_refreshView '

DECLARE viewCsr CURSOR READ_ONLY FAST_FORWARD FOR
SELECT sob.name
FROM sysobjects sob WITH (NOLOCK)
LEFT OUTER JOIN information_schema.view_table_usage vtu ON sob.name = vtu.view_name
WHERE sob.xtype = 'V'
AND sob.name NOT LIKE 'sys%'
--AND @tables IS NULL
--OR CHARINDEX(OBJECT_NAME(vtu.table_name), N',' + @tables + N',') > 0

OPEN viewCsr

FETCH NEXT FROM viewCsr INTO @view
WHILE @@FETCH_STATUS = 0
BEGIN
      PRINT @sql + @view
      EXEC(@sql + @view)      
      FETCH NEXT FROM viewCsr INTO @view
END

DEALLOCATE viewCsr
0
 

Author Comment

by:rmk
ID: 10786068
That's getting pretty close to what I need. Since I'm an application developer and not a dba I was not aware of sp_refreshview which I now think of as a great tool. I've tested how it works and it's a big help. That still leaves me with the problem of nested views and order of dependency, i.e. somehow I have to be smart enough to do the refreshes from the lowest levels moving up to the topmost levels or the refresh is meaningless. So if I add a column to a table, I need to walk through all the select * views that use that table from bottom to top; or if I add a column to a vies, I need to walk through all the select * views that use that view from botton to top. As I document all of this it's becoming clearer to me that there is probably not a quick fix and that I need to more carefully evaluate the impact of such changes and be prepared to meticulously walk through all of my object dependencies.
0
 
LVL 3

Expert Comment

by:jayrod
ID: 10786096
I'm tellin ya... you love schema binding... it's the trick :P j/k

ya it doesn't seem that there is an easy solution
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1000 total points
ID: 10786180
Honestly, to deal with nested views, just run it two (or three, if necessary).  The first time will get the lowest-level views, the next run will get the next-up views, and so on.
0
 

Author Comment

by:rmk
ID: 10786261
Last time I tried to play with schema binding I ran into all kinds of restrictions. Guess it's time I make a commitment to learning how to use this stuff.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

722 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