| Author |
 Topic  |
|
|
ldo
Starting Member
5 Posts |
 Posted - 09/26/2007 : 15:16:05
|
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
29138 Posts |
Posted - 09/26/2007 : 15:22:08
|
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" |
 |
|
|
ldo
Starting Member
5 Posts |
Posted - 09/26/2007 : 15:27:25
|
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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/26/2007 : 15:33:56
|
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" |
|
|
|
ldo
Starting Member
5 Posts |
Posted - 09/26/2007 : 17:03:09
|
Hi Peso,
Sorry Peso, I don't get it. I need the datatype on view need to change. The application complain. Thanks
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/26/2007 : 17:06:32
|
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" |
|
|
|
ldo
Starting Member
5 Posts |
Posted - 09/26/2007 : 23:10:41
|
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
|
|
|
|
pravin14u
Posting Yak Master
India
246 Posts |
Posted - 09/27/2007 : 00:39:42
|
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 |
|
|
|
ldo
Starting Member
5 Posts |
Posted - 09/27/2007 : 02:08:36
|
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 |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/27/2007 : 05:16:00
|
Another reason to NEVER use
SELECT *
in production environment. Or ever.
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
Sparrow7
Starting Member
1 Posts |
Posted - 09/24/2008 : 10:10:57
|
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
|
|
|
| |
Topic |
|
Update table and need update view
