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 tabl
e
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
>
>

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
|||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
>
>

ALTER TABLE ALTER COLUMN [access-id] failed because one or more objects access this column

Hi

when I'm upgrading table schema with alter statement

I'm getting error like this

ALTER TABLE ALTER COLUMN [access-id] failed because one or more objects access this column

can Anybody tell the solutiuon plz.

Thank u .

vizai

Please check to see if you have foreign key(s) referencing the column.|||

hi Waldrop

The column has a constraint

|||

If you have any View /UDF With SchemaBinding on this Base table , you are not allowed to change or Drop columns or Table Object..

If you want to alter the table first you need to remove the SchemaBinding in all the views/UDF and change the column on Base table then recreate the Views/UDFs With SchemaBinding.

If you have any Indexed Views when you remove the SchemaBinding all the Index on the Views will be removed, so you have to create them too..

|||

Hi mani

Thanks fo the reply.

How can I use schemabinding to drop the views / udf / indexed views using SMO.

How can I generate the drop script alone for all the views / udf / indexed views / constraints.

Can u help me in this

ALTER TABLE ALTER COLUMN [access-id] failed because one or more objects access this column

Hi

when I'm upgrading table schema with alter statement

I'm getting error like this

ALTER TABLE ALTER COLUMN [access-id] failed because one or more objects access this column

can Anybody tell the solutiuon plz.

Thank u .

vizai

Please check to see if you have foreign key(s) referencing the column.|||

hi Waldrop

The column has a constraint

|||

If you have any View /UDF With SchemaBinding on this Base table , you are not allowed to change or Drop columns or Table Object..

If you want to alter the table first you need to remove the SchemaBinding in all the views/UDF and change the column on Base table then recreate the Views/UDFs With SchemaBinding.

If you have any Indexed Views when you remove the SchemaBinding all the Index on the Views will be removed, so you have to create them too..

|||

Hi mani

Thanks fo the reply.

How can I use schemabinding to drop the views / udf / indexed views using SMO.

How can I generate the drop script alone for all the views / udf / indexed views / constraints.

Can u help me in this

alter column statement doesn't work, trying to change datatype

I'm trying to do something like this:
ALTER TABLE [tablename] ALTER COLUMN [fieldID] int;
I've tried several versions of this, and the data type is never changed from
numeric. I have removed all indexes on the table and it still doesn't work,
no error messages or anything. I am trying to do this with script because I
have to change like 150 databases and it's part of a much bigger script that
I have to do the same thing on other tables and columns. Any ideas?
thanks,
CoryPerhaps you are altering some other table. Have you checked the owner of the
table? Or, better yet,
owner-qualify the ALTER statement.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:Ob$zDhUNFHA.3192@.TK2MSFTNGP10.phx.gbl...
> I'm trying to do something like this:
> ALTER TABLE [tablename] ALTER COLUMN [fieldID] int;
> I've tried several versions of this, and the data type is never changed fr
om numeric. I have
> removed all indexes on the table and it still doesn't work, no error messa
ges or anything. I am
> trying to do this with script because I have to change like 150 databases
and it's part of a much
> bigger script that I have to do the same thing on other tables and columns
. Any ideas?
>
> thanks,
> Cory
>
>|||No error messages -- are you sure it's not working? What do you see when
you run sp_help 'tablename' ?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:Ob$zDhUNFHA.3192@.TK2MSFTNGP10.phx.gbl...
> I'm trying to do something like this:
> ALTER TABLE [tablename] ALTER COLUMN [fieldID] int;
> I've tried several versions of this, and the data type is never changed
from
> numeric. I have removed all indexes on the table and it still doesn't
work,
> no error messages or anything. I am trying to do this with script because
I
> have to change like 150 databases and it's part of a much bigger script
that
> I have to do the same thing on other tables and columns. Any ideas?
>
> thanks,
> Cory
>
>|||Wow, you're right, running sp_help does show that it was converted from
numeric to int. I have been looking at it through Enterprise Manager. I
can run the script then look at the design view for the table and it is
unchanged even after closing everything out, refreshing everything, and
trying again. I am becoming more and more agitated with Enterprise Manager
by the day.
thank you,
Cory
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23j3gApUNFHA.2544@.TK2MSFTNGP10.phx.gbl...
> No error messages -- are you sure it's not working? What do you see when
> you run sp_help 'tablename' ?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Cory Harrison" <charrison@.csiweb.com> wrote in message
> news:Ob$zDhUNFHA.3192@.TK2MSFTNGP10.phx.gbl...
> from
> work,
> I
> that
>|||> trying again. I am becoming more and more agitated with Enterprise
Manager
> by the day.
And you haven't even read http://www.aspfaq.com/2455 yet...