Add a field to multiple views, is possible?

Question

I have to add a column, a field to multiple views in SQL Server. This is an example of a view that I'm using, where tbxxx stay for a table and vwxxx for a view:

ALTER VIEW [dbo].[vwFornitori_Search]
AS
    SELECT IDitem,
           IDana,
           codice,
           ragione_sociale,
           c.valore AS colore
    FROM tbAnagrafiche
    LEFT JOIN tbColori c ON tbAnagrafiche.colore = c.IDcolore
    WHERE IDitem = 'FOR'
        AND ISNULL(eliminato, 0) = 0
        AND ISNULL(obsoleto, 0) = 0
GO

I have to add to all my views another field, another column, that is always the principal table's primary key! The modified view will be:

ALTER VIEW [dbo].[vwFornitori_Search]
AS
    SELECT IDitem,
           IDana,
           codice,
           ragione_sociale,
           c.valore AS colore,
           IDana AS ID
    FROM tbAnagrafiche
    LEFT JOIN tbColori c ON tbAnagrafiche.colore = c.IDcolore
    WHERE IDitem = 'FOR'
        AND ISNULL(eliminato, 0) = 0
        AND ISNULL(obsoleto, 0) = 0
GO

Usually the primary key has always the same name, like IDana. There's a way to do that with a single script to a list of views ?

You can use sys.sysobjects, sys.syscolumns, sys.syscomments to make a query to which would generate scripts. Check below answer for reference. http://stackoverflow.com/questions/35044927/update-multiple-tables-with-the-same-column/35045102#35045102 — Faisal, Jan 28 '16 at 12:19

score 0 · Accepted Answer · answered Jan 28 '16 at 12:39

You could do this by following these few steps:

1. Extract the SQL of the views' definitions:

SELECT CONCAT(m.definition, ';') 
FROM   sys.sql_modules m, sys.views v
WHERE  m.object_id = v.object_id
AND    v.name in ('myview1', 'myview2');

This outputs a list of SQL statements in the form:

CREATE VIEW myview1 (mycol1, mycol2) AS
SELECT ...;
CREATE VIEW myview1 (mycol1, mycol2, mycol3) AS
SELECT ...;

2. Manipulate the SQL

Copy/paste the above output in a text editor and perform an intelligent find/replace to insert the additional column in your select list.

The most simple find/replace would be:

Find: "FROM"
Replace: ", IDana AS ID FROM"

But this will not work if you have nested SELECT statements in your views. In that case you should use regular expressions, if your editor supports them, to make sure the replacement happens exactly where it should.

A regular expression like this would do it:

Find: "/(CREATE VIEW.*?SELECT.*?)FROM/gi"
Replace: "$1, IDana AS ID FROM"

Finally, replace all CREATE VIEW occurrences by ALTER VIEW.

3. Execute the final SQL

Copy/paste the manipulated SQL statements back into your database environment and execute them as a batch.

Add a field to multiple views, is possible?

1 Answers1

1. Extract the SQL of the views' definitions:

2. Manipulate the SQL

3. Execute the final SQL