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
 General SQL Server Forums
 New to SQL Server Programming
 Update table and need update view
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ldo
Starting Member

5 Posts

Posted - 09/26/2007 :  15:16:05  Show Profile  Reply with Quote
Please help.
give you example
I have table = Product
and column = Product_name varchar(20)

Then I have view call v_product this view select * from product.

Each time I modify product type become varchar(25). My view v_product didn't get change. I have to alter the view

The question I have. I have a lot of tables more then 80 need to change the data type. Any one have best way refesh the view??? Thank you


SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 09/26/2007 :  15:22:08  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You DO NOT have to change the view.
It returns the same datatype as the column, with same length.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

ldo
Starting Member

5 Posts

Posted - 09/26/2007 :  15:27:25  Show Profile  Reply with Quote
Hi Peso,
I changed datatype varchar (25)for this table and the view till see varchar (20). If I want to get the same. I have to alter the view. Do you have any good way to do this? Because I have a lot of table need to change. Thanks
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 09/26/2007 :  15:33:56  Show Profile  Visit SwePeso's Homepage  Reply with Quote
The view has NO stored information about columns.
It fetches and uses the underlying table definition for this.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

ldo
Starting Member

5 Posts

Posted - 09/26/2007 :  17:03:09  Show Profile  Reply with Quote
Hi Peso,
Sorry Peso, I don't get it. I need the datatype on view need to change. The application complain. Thanks
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 09/26/2007 :  17:06:32  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You can CAST or CONVERT the columns in the view, tha you fetch from the underlying table.

If column1 in table1 is INT, and column2 is varchar(50), you can cast it in the view as

CREATE VIEW dbo.vwTwit
SELECT CAST(Column1 AS VARCHAR(12)), convert(varchar(20), column2) FROM Table1



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

ldo
Starting Member

5 Posts

Posted - 09/26/2007 :  23:10:41  Show Profile  Reply with Quote
Hello Peso.
Please help.
My question is how to refesh the views when I change the datatype column in table. Note. The view existing in data base. When I change the datatype inthe table column varchar (20) to varchar(25). The existing view in database didn't refesh. Thanks
Go to Top of Page

pravin14u
Posting Yak Master

India
246 Posts

Posted - 09/27/2007 :  00:39:42  Show Profile  Click to see pravin14u's MSN Messenger address  Send pravin14u a Yahoo! Message  Reply with Quote
Hi,

Right click on the view -> Edit . Now the view will open in a query window (with Alter View...). Now execute the View again (Run). Now you can see the changes reflected. I believe you need to do this for all the views to reflect the change in the table.

Edited by - pravin14u on 09/27/2007 00:40:18
Go to Top of Page

ldo
Starting Member

5 Posts

Posted - 09/27/2007 :  02:08:36  Show Profile  Reply with Quote
Hi Pravin14U,
Do you have any short cut to refesh the view? I have a lot of views to change. Go alter view one by one that is pain. Thanks
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 09/27/2007 :  05:02:05  Show Profile  Visit spirit1's Homepage  Reply with Quote
you can use sp_refreshview 'viewName'




_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 09/27/2007 :  05:16:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Another reason to NEVER use

SELECT *

in production environment. Or ever.





E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Sparrow7
Starting Member

1 Posts

Posted - 09/24/2008 :  10:10:57  Show Profile  Reply with Quote
I know this is a year later, but this will be helpful to someone in the same situation. ...I've never had a problem with SELECT * ...

declare @t_obj table(rowid int identity, obj nvarchar(128), xtype varchar(2))
declare @rowid int, @obj nvarchar(128)


insert into @t_obj (obj, xtype)
select name, xtype
from sysobjects
where xtype in ('V')
and status>0
order by name
select @rowid = @@rowcount

while @rowid > 0
begin
select @obj = obj from @t_obj where rowid = @rowid
exec sp_refreshview @obj
print 'Refreshed ' + @obj
select @rowid = @rowid - 1
end

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.11 seconds. Powered By: Snitz Forums 2000