Automated way to update a view code using sql script?

Question

Lets suppose i have a table A with 4 columns and I've created a view for the same table which consists of all 4 columns. Now i want to write a script/stored procedure which should update the view code whenever new column is added to Table A in a same physical order.

For Example: Table A has following columns

'Name','Age','Gender','Address'

Supposed view code is:

Select Name,Age,Gender,Address from Table A

Now i add an additional column PhoneNumber on Table A. When i run script/Stored Procedure this new column should automatically be added to the query as follows:

Desired view code generated after i run script should be like this:

Select Name,Age,Gender,Address,PhoneNumber from Table A

@AfzaalAhmad, I removed the MySQL tag from your question since that database is not Microsoft SQL Server. — Dan Guzman, Nov 13 '17 at 10:38
@Sami, one should avoid using `select *` in views because it is well known scenario when table structure changes and view begins to return wrong data unless explicitly altered (rebuilt). — Ivan Starostin, Nov 13 '17 at 11:29

score 1 · Answer 1 · answered Nov 13 '17 at 10:43

1

If I understand what are you asking, then why you need to specify the columns, instead of that change your code in the view to:

SELECT * FROM TableA

In this way the View will always return all columns in that table.

answered Nov 13 '17 at 10:43

Ilyes

14,640
4
29
55

Actually i have 6 tables with almost 3000 columns each, i want to automate this process so i run a stored proc and pass it keytable,keyobject as parameter and it sync the code with actual columns. Secondly, due to architectural constraints have to specify all columns by name rather than using * – Afzaal Ahmad Nov 13 '17 at 10:46

score 1 · Answer 2 · answered Nov 13 '17 at 10:52

Below is an example that will alter the view, specifying an explicit column list.

DECLARE @AltereViewSQL nvarchar(MAX);
SELECT @AltereViewSQL = N'ALTER VIEW dbo.YourView AS SELECT '
    + STUFF((SELECT ','  + name
    FROM sys.columns
    WHERE 
        object_id = OBJECT_ID(N'dbo.YourTable')
    ORDER BY
        column_id
    FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)'), 1, 1, '')
    + ' FROM dbo.YourTable;'

EXEC(@AltereViewSQL);

score 1 · Accepted Answer · answered Nov 13 '17 at 11:06

1

Hope It will works

    DECLARE @tablename as varchar(20)
    DECLARE @query AS NVARCHAR(MAX)
    DECLARE @ColumnName AS NVARCHAR(MAX)
    set @tablename='tableName'
    SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
            + QUOTENAME(COLUMN_NAME)
    FROM ( SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = @tablename
                )  as ccc
    set @query='select '+@ColumnName+' from '+@tablename+''
    EXEC (@query)

answered Nov 13 '17 at 11:06

nazmul.3026

918
1
9
20

1

Note that this will not necessarily return the columns in the same physical order and [aggregate string concatenation behavior is undefined](https://stackoverflow.com/questions/44223465/string-aggregate-using-a-variable). `STRING_AGG` is the best solution in SQL 2017 and later but FOR XML is needed in earlier versions. – Dan Guzman Nov 13 '17 at 13:10
Thanks @DanGuzman for your suggestion.I actually wrote this for all versions. – nazmul.3026 Nov 13 '17 at 14:11

score 1 · Answer 4 · answered Nov 13 '17 at 14:46

Side note as a caution from using select * in views:

repro:

CREATE TABLE t (id INT, NAME VARCHAR(10))
GO
INSERT INTO t VALUES (1, 'a')
GO
CREATE VIEW v AS SELECT * FROM t
GO
SELECT * FROM v
GO
ALTER TABLE t ADD d DATE NOT NULL DEFAULT (GETDATE())
GO
ALTER TABLE t DROP COLUMN id
GO
SELECT * FROM v
GO

DROP VIEW v
GO
DROP TABLE t
GO

now put before drops this:

SELECT * FROM v
WHERE id = 3

you'll get

Conversion failed when converting the varchar value 'a' to data type int.

Instead of invalid object name id. Which means than in some cases your solution will keep working relying on absolutely wrong data!

Replace 'a' in sample data with '3' to make implicit conversion succeed and this code will work, will return some data. Whilst there never was id=3 and at the time of running this select there is physically even no id column at all.

Automated way to update a view code using sql script?

4 Answers4