sql - Add a field to multiple views, is possible? -

i have add column, field multiple views in sql server. example of view i'm using, tbxxx stay table , vwxxx view:

alter view [dbo].[vwfornitori_search]     select iditem,            idana,            codice,            ragione_sociale,            c.valore colore     tbanagrafiche     left join tbcolori c on tbanagrafiche.colore = c.idcolore     iditem = 'for'         , isnull(eliminato, 0) = 0         , isnull(obsoleto, 0) = 0 go 

i have add views field, column, principal table's primary key! modified view be:

alter view [dbo].[vwfornitori_search]     select iditem,            idana,            codice,            ragione_sociale,            c.valore colore,            idana id     tbanagrafiche     left join tbcolori c on tbanagrafiche.colore = c.idcolore     iditem = 'for'         , isnull(eliminato, 0) = 0         , isnull(obsoleto, 0) = 0 go 

usually primary key has same name, idana. there's way single script list of views ?

you following these few steps:

1. extract sql of views' definitions:

select concat(m.definition, ';')    sys.sql_modules m, sys.views v  m.object_id = v.object_id ,    v.name in ('myview1', 'myview2'); 

this outputs list of sql statements in form:

create view myview1 (mycol1, mycol2) select ...; create view myview1 (mycol1, mycol2, mycol3) select ...; 

2. manipulate sql

copy/paste above output in text editor , perform intelligent find/replace insert additional column in select list.

the simple find/replace be:

find: "from"
replace: ", idana id from"

but not work if have nested select statements in views. in case should use regular expressions, if editor supports them, make sure replacement happens should.

a regular expression it:

find: "/(create view.*?select.*?)from/gi"
replace: "$1, idana id from"

finally, replace create view occurrences alter view.

3. execute final sql

copy/paste manipulated sql statements database environment , execute them batch.