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 viewI 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_recompilei gave my base table but it still didnt worked, i guess, it re-compiles only sps and triggers not views; sad |
|
|
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. |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2008-02-14 : 08:15:13
|
sp_refresh_viewJayto here knows when |
|
|
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 Shawwww.SQLonCall.com |
|
|
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 |
|
|
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. |
|
|
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 |
|
|
dev2dev
Starting Member
48 Posts |
Posted - 2008-04-02 : 03:06:13
|
I am thinking to create a script/sp that does belowloops through user viewsfor each view get text from syscomments and store in a variable (say @sql) drop the view prepare dynamic create view using the stored variable @sqlnextany thoughtsThanks for all the discussion |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-02 : 22:32:34
|
>> sp_refresh_viewShould be sp_refreshview, that's what you need. |
|
|
dev2dev
Starting Member
48 Posts |
Posted - 2008-04-03 : 08:23:49
|
thank you so much |
|
|
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 |
|
|
|