27

I have created a view that is based on another view and a table. I want to add new column of type varchar. I did like below, But getting syntax error? I am new to SQL, So,could not understand

ALTER VIEW [dbo].[MyView]
ADD New_Col varchar(10) null 
GO
SPBeginer
  • 733
  • 3
  • 14
  • 29
  • What's the error message? Help us help you. – Shiva Sep 16 '16 at 15:36
  • 1
    Not sure what you mean by creating a view based on another view but if you views are nested you have a performance issue. Nested views seem so logical but in reality they are just awful. https://www.simple-talk.com/sql/performance/the-seven-sins-against-tsql-performance/ – Sean Lange Sep 16 '16 at 15:40

4 Answers4

31

you have to write the entire view again and just add or omit what you want to change

for example your view is now :

create view myView as
  select field1
  from table1

and now you want to add a field called New_Col than you write this :

alter view myView as
  select field1,
         New_Col
  from table1
GuidoG
  • 11,359
  • 6
  • 44
  • 79
  • can't we write a script for adding a column for all the views in a database? – manikanta nvsr Jun 19 '20 at 05:44
  • @manikantanvsr Yes, you could by using select * or by using select table_name.* However, remember that the view won't automatically pick up new columns from the table. After you alter the table, you'll need to refresh the view using SP_REFRESHVIEW "view_name". – Aditya Kar Oct 31 '22 at 14:11
  • @AdityaKar I will never recommend `select *` to anyone, that is just bad practice. And certainly not in a view ! – GuidoG Oct 31 '22 at 14:23
3

You can't alter a view like a table. You have to script the view as Alter, and then alter the select statement that generates the view.

BeardOfTriumph
  • 71
  • 5
0

You cannot change a view’s SELECT statement with the ALTER VIEW statement. Instead, you must drop and re-create the view.

Nagendra Nainala
  • 17
  • 2
  • 1
    There is no need to drop the view, you can use `alter view` but not like you would with a table. See my answer – GuidoG Nov 18 '20 at 06:37
-1
if not exists(select * from sys.columns where (name='columnToBeAdd') and object_id=object_id('YourViewName'))
begin
Declare @ViewDefinition varchar(max) 
select @ViewDefinition=definition from sys.objects o join sys.sql_modules m on m.object_id=o.object_id
where o.object_id=object_id('YourViewName') 
and o.type ='V'
select 
@ViewDefinition=REPLACE( replace(@ViewDefinition,'CREATE ','Alter '),' Create ' ,' Alter ' ) 
Select @ViewDefinition=REPLACE( @ViewDefinition, 'From ',', YourTableName.columnToBeAdd + CHAR(13) +  ' From ' )

exec( @ViewDefinition)
end
Suraj Rao
  • 29,388
  • 11
  • 94
  • 103
Wael Melhi
  • 1