SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Other SQL Server Topics (2005)
 Can we recompile view instead of drop and create
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dev2dev
Starting Member

India
48 Posts

Posted - 01/31/2008 :  01:01:16  Show Profile  Reply with Quote
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

India
48 Posts

Posted - 01/31/2008 :  01:12:24  Show Profile  Reply with Quote
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 - 02/13/2008 :  10:36:04  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 02/14/2008 :  08:15:13  Show Profile  Reply with Quote
sp_refresh_view

Jay
to here knows when
Go to Top of Page

CShaw
Yak Posting Veteran

65 Posts

Posted - 02/16/2008 :  00:36:26  Show Profile  Visit CShaw's Homepage  Reply with Quote
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

India
48 Posts

Posted - 04/02/2008 :  02:55:29  Show Profile  Reply with Quote
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

India
48 Posts

Posted - 04/02/2008 :  03:00:50  Show Profile  Reply with Quote
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

India
48 Posts

Posted - 04/02/2008 :  03:03:54  Show Profile  Reply with Quote
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

India
48 Posts

Posted - 04/02/2008 :  03:06:13  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 04/02/2008 :  22:32:34  Show Profile  Reply with Quote
>> sp_refresh_view

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

dev2dev
Starting Member

India
48 Posts

Posted - 04/03/2008 :  08:23:49  Show Profile  Reply with Quote
thank you so much
Go to Top of Page

neilmsql
Starting Member

United Kingdom
1 Posts

Posted - 07/03/2012 :  16:17:06  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000