Hello all!
I think I asked this question in a different way a long time ago, and wasn't sure if there
was any resolution.
Consider the following DDL/DML:
create table tTest (Id int)
go
create view vTest
as
select * from tTest
go
select * from vTest
go
alter table tTest add Name varchar(255)
go
select * from vTest
go
Is there any easy way, short of DROPping/CREATEing or ALTERing the VIEW, to have the VIEW
automatically reflect the changes to the underlying table? I had hoped a sp_recompile
might do the trick, but alas. :-(
Thanks for any help you can provide!
John PetersonThis is a multi-part message in MIME format.
--=_NextPart_000_005E_01C36B1C.27177190
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Check out sp_refreshview in the BOL.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:OmfK0zzaDHA.880@.TK2MSFTNGP09.phx.gbl...
(SQL Server 2000, SP3a)
Hello all!
I think I asked this question in a different way a long time ago, and =wasn't sure if there
was any resolution.
Consider the following DDL/DML:
create table tTest (Id int)
go
create view vTest
as
select * from tTest
go
select * from vTest
go
alter table tTest add Name varchar(255)
go
select * from vTest
go
Is there any easy way, short of DROPping/CREATEing or ALTERing the VIEW, =to have the VIEW
automatically reflect the changes to the underlying table? I had hoped =a sp_recompile
might do the trick, but alas. :-(
Thanks for any help you can provide!
John Peterson
--=_NextPart_000_005E_01C36B1C.27177190
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Check out sp_refreshview in the =BOL.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson"
--=_NextPart_000_005E_01C36B1C.27177190--|||John,
I think you are searching fot sp_refreshview system SP. Check it in Books
OnLine.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OmfK0zzaDHA.880@.TK2MSFTNGP09.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I think I asked this question in a different way a long time ago, and
wasn't sure if there
> was any resolution.
> Consider the following DDL/DML:
>
> create table tTest (Id int)
> go
> create view vTest
> as
> select * from tTest
> go
> select * from vTest
> go
> alter table tTest add Name varchar(255)
> go
> select * from vTest
> go
>
> Is there any easy way, short of DROPping/CREATEing or ALTERing the VIEW,
to have the VIEW
> automatically reflect the changes to the underlying table? I had hoped a
sp_recompile
> might do the trick, but alas. :-(
> Thanks for any help you can provide!
> John Peterson
>|||John,
Try this:
create table tTest (Id int)
go
create view vTest
as
select * from tTest
go
select * from vTest
go
alter table tTest add Name varchar(255)
go
sp_refreshview vTest
go
select * from vTest
go
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OmfK0zzaDHA.880@.TK2MSFTNGP09.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I think I asked this question in a different way a long time ago, and
wasn't sure if there
> was any resolution.
> Consider the following DDL/DML:
>
> create table tTest (Id int)
> go
> create view vTest
> as
> select * from tTest
> go
> select * from vTest
> go
> alter table tTest add Name varchar(255)
> go
> select * from vTest
> go
>
> Is there any easy way, short of DROPping/CREATEing or ALTERing the VIEW,
to have the VIEW
> automatically reflect the changes to the underlying table? I had hoped a
sp_recompile
> might do the trick, but alas. :-(
> Thanks for any help you can provide!
> John Peterson
>|||Thanks, all! That was a speedy and accurate response! :-)
The sp_refreshview is exactly the type of thing I was looking for.
I think my other (somewhat related) thread was in reference to changing the
ansi_null/quoted_identifier settings of a VIEW/SP without having to DROP/CREATE or ALTER
that VIEW. It sounded as if there wasn't a convenient way to do that (though, someone had
come up with a clever solution to "dinking" the sysobjects table).
In any event, thanks again! :-)
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OmfK0zzaDHA.880@.TK2MSFTNGP09.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I think I asked this question in a different way a long time ago, and wasn't sure if
there
> was any resolution.
> Consider the following DDL/DML:
>
> create table tTest (Id int)
> go
> create view vTest
> as
> select * from tTest
> go
> select * from vTest
> go
> alter table tTest add Name varchar(255)
> go
> select * from vTest
> go
>
> Is there any easy way, short of DROPping/CREATEing or ALTERing the VIEW, to have the
VIEW
> automatically reflect the changes to the underlying table? I had hoped a sp_recompile
> might do the trick, but alas. :-(
> Thanks for any help you can provide!
> John Peterson
>
No comments:
Post a Comment