Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Other SQL Server Topics (2005)
 Can we recompile view instead of drop and create

Author  Topic 

dev2dev
Starting Member

48 Posts

Posted - 2008-01-31 : 01:01:16
Hi,
I increased one of my base tables column which is referenced in view

I noticed sql server didn't recognized this change and its still showing old field size in the view.

I can simply drop and create it again. But wanted to know if there is any way (command/sp) to recompile the view which will be easy to deploy in production as patch.

Thanks
- D

dev2dev
Starting Member

48 Posts

Posted - 2008-01-31 : 01:12:24
fyi:

i just found sp_recompile

i gave my base table but it still didnt worked, i guess, it re-compiles only sps and triggers not views; sad
Go to Top of Page

Sara Karasik
Starting Member

10 Posts

Posted - 2008-02-13 : 10:36:04
Script the view as Modify, and run it.

Modify ( Alter View) is preferable to drop and recreate. If you drop and recreate, you lose all permissions that you may have granted on the view. You also lose the create date, and possibly statistics on the usage of the view as well.
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2008-02-14 : 08:15:13
sp_refresh_view

Jay
to here knows when
Go to Top of Page

CShaw
Yak Posting Veteran

65 Posts

Posted - 2008-02-16 : 00:36:26
This may be a little late for the post but does you view do a Select * ? If this is the case I would recommend that you do change it to specific columns. This will help avoiding the re-compile of your view issue all together, however if you do add a new column that the view needs to see you will have to alter it to add it.



Chris Shaw
www.SQLonCall.com
Go to Top of Page

dev2dev
Starting Member

48 Posts

Posted - 2008-04-02 : 02:55:29
quote:
Originally posted by Page47

sp_refresh_view




Is that a real/system sp? I could not find it
Go to Top of Page

dev2dev
Starting Member

48 Posts

Posted - 2008-04-02 : 03:00:50
quote:
Originally posted by Sara Karasik

Script the view as Modify, and run it.

Modify ( Alter View) is preferable to drop and recreate. If you drop and recreate, you lose all permissions that you may have granted on the view. You also lose the create date, and possibly statistics on the usage of the view as well.



I don't have issues from permission perspective. Only concern is I have to run ALTER or DROP and CREATE view step to refresh the view when ever I modify table structure. If there is any way to refresh the view without using the view text then I can simply execute that sql/step/sp for all my/user views.

Go to Top of Page

dev2dev
Starting Member

48 Posts

Posted - 2008-04-02 : 03:03:54
quote:
Originally posted by CShaw

This may be a little late for the post but does you view do a Select * ? If this is the case I would recommend that you do change it to specific columns. This will help avoiding the re-compile of your view issue all together, however if you do add a new column that the view needs to see you will have to alter it to add it.


There are some views that has "select *" but I have this problem when I modify the data type aswell. Example, modify column from DECIMAL (5,2) to DECIMAL(9,4) the dependent view will still show old data type DECIMAL (5,2) so refreshing this I need to ALTER or DROP and CREATE the view
Go to Top of Page

dev2dev
Starting Member

48 Posts

Posted - 2008-04-02 : 03:06:13
I am thinking to create a script/sp that does below
loops through user views
for each view
get text from syscomments and store in a variable (say @sql)
drop the view
prepare dynamic create view using the stored variable @sql
next

any thoughts

Thanks for all the discussion
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-02 : 22:32:34
>> sp_refresh_view

Should be sp_refreshview, that's what you need.
Go to Top of Page

dev2dev
Starting Member

48 Posts

Posted - 2008-04-03 : 08:23:49
thank you so much
Go to Top of Page

neilmsql
Starting Member

1 Post

Posted - 2012-07-03 : 16:17:06
quote:
Originally posted by Sara Karasik

Script the view as Modify, and run it.

Modify ( Alter View) is preferable to drop and recreate. If you drop and recreate, you lose all permissions that you may have granted on the view. You also lose the create date, and possibly statistics on the usage of the view as well.



In addition to this if you have any triggers associated with the view these will be lost too.
We had an issue today where one of our DBA's scripted out a view, dropped it, recreated it by running the script, and found that users started getting "update or insert of view or function <view name> failed because it contains a derived or constant field"
This was because of the missing trigger.
The triggers are in the "triggers" folder under the view definition in Management Studio.

SQL DBA
Go to Top of Page
   

- Advertisement -