ALTER TABLE statement does not change view based on table

Hi,
Whenever I add a column to a Table like this
ALTER TABLE xxx {Add Column}
it never adds that column to the view that I have created based on that table
The view T-SQL for the view goes like this
SELECT * FROM xxx
Since I want to return all fields it never includes the newly created
columns that I created using ALTER Table.
When I drop the view and recreate this, it the view works fine.
How can I fix this problem?
Thanks,
Andre"Spongebob76" <andre.beier@.community.nospam> wrote in message
news:BF37273C-661D-4071-A0F8-EB330F444A75@.microsoft.com...
> Hi,
> Whenever I add a column to a Table like this
> ALTER TABLE xxx {Add Column}
> it never adds that column to the view that I have created based on that
> table
> The view T-SQL for the view goes like this
> SELECT * FROM xxx
> Since I want to return all fields it never includes the newly created
> columns that I created using ALTER Table.
> When I drop the view and recreate this, it the view works fine.
> How can I fix this problem?
You really can't.
Besides, I'm not sure why you're doing what you you're doing.
Firstly, Select * from is bad technique.
Secondly, why are you using this in a view. Might as well simply call the
base table.
> Thanks,
> Andre
>
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||"Spongebob76" <andre.beier@.community.nospam> wrote in message
news:BF37273C-661D-4071-A0F8-EB330F444A75@.microsoft.com...
> Hi,
> Whenever I add a column to a Table like this
> ALTER TABLE xxx {Add Column}
> it never adds that column to the view that I have created based on that
> table
> The view T-SQL for the view goes like this
> SELECT * FROM xxx
> Since I want to return all fields it never includes the newly created
> columns that I created using ALTER Table.
> When I drop the view and recreate this, it the view works fine.
> How can I fix this problem?
> Thanks,
> Andre
>
>
Don't use SELECT * in views. If you want an alias for a table then use a
synonym instead.
sp_refreshview updates the view metadata but if you avoid SELECT * then
you'll never need to use it!
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Andre
As Greg said, using SELECT * is not good practice, and that is especially
true for views, for just this reason.
That being said, you can take a look at sp_refreshview.
In the future, always let us know what version you are running.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Spongebob76" <andre.beier@.community.nospam> wrote in message
news:BF37273C-661D-4071-A0F8-EB330F444A75@.microsoft.com...
> Hi,
> Whenever I add a column to a Table like this
> ALTER TABLE xxx {Add Column}
> it never adds that column to the view that I have created based on that
> table
> The view T-SQL for the view goes like this
> SELECT * FROM xxx
> Since I want to return all fields it never includes the newly created
> columns that I created using ALTER Table.
> When I drop the view and recreate this, it the view works fine.
> How can I fix this problem?
> Thanks,
> Andre
>
>