Update table and need update view

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

General SQL Server Forums

New to SQL Server Programming

Update table and need update view

Author

Topic

ldo
Starting Member

5 Posts

Posted - 2007-09-26 : 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

30421 Posts

Posted - 2007-09-26 : 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 - 2007-09-26 : 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

30421 Posts

Posted - 2007-09-26 : 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 - 2007-09-26 : 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

30421 Posts

Posted - 2007-09-26 : 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 - 2007-09-26 : 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

246 Posts

Posted - 2007-09-27 : 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.

ldo
Starting Member

5 Posts

Posted - 2007-09-27 : 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

11752 Posts

Posted - 2007-09-27 : 05:02:05

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-27 : 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 Post

Posted - 2008-09-24 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources