CODEWITHSUNDEEP
sqlsql-server
Alex Schenkel
Alex Schenkel

Reputation: 832

Columns in views get displaced when altering table in MS-SQL

I am occasionally forced to use MS-SQL, so my knowledge of it is basic. I'm sure the following problem is piece of cake for an MS-SQL expert, but for me this looks really like a nasty bug:

Say I have a simple table containing a person's last name, first name, and email, like this:

CREATE TABLE dbo.people
(
lastName varchar(50) NULL,
firstName varchar(50) NULL,
email varchar(50) NULL
)

Now I'm going to create a view so I have some pre-concated fields, like this:

CREATE VIEW v_people AS
SELECT
    people.*,
    people.lastName + ' ' + people.firstName AS concatName
FROM people

Now I notice that I forgot the Phone number field, and alter the original table, but ONLY the table, NOT the view:

ALTER TABLE dbo.people ADD
phone varchar(50) NULL

Cool, also works. But what now happened to the view is quite horrible: The view field 'concatName' does not contain the concated names, but the Phone Numbers, even if the view fields are STILL the same amount and names!

It seems that MS-SQL is just moving the data columns within the view without updating the column definitions. Quite horrible scenario, if you forget to update views that depends on tables you alter...

Do I have overseen something, or am I really responsible for always checking / altering all the views if I just add a new table column? Is there a way to let the MS-SQL server at least throw a warning about depending views?

I noticed that it does not happen if the view is constructed like this:

CREATE VIEW v_people AS
SELECT
    people.lastName + ' ' + people.firstName AS concatName,
    people.*
FROM people

But for me this just looks like a nasty workaround....

Any hints?

Upvotes: 4

Views: 3424

Answers (3)

Daniel Frühauf
Daniel Frühauf

Reputation: 311

As already answered years ago, the solution is to use sp_refreshview 'YourView' for the affected views.

Since one of the follow-up questions was if you need to do it for each view manually afterwards, I want to answer that: You could search for views that have a * in their definition (surrounded by SELECT and FROM) by using sys.sql_modules.

  SELECT schemas.name 'schema',
         objects.name 'object',
         sql_modules.definition 'definition'
    FROM sys.sql_modules
    JOIN sys.objects ON sql_modules.object_id=objects.object_id
    JOIN sys.schemas ON objects.schema_id=schemas.schema_id
   WHERE definition LIKE '%SELECT%*%FROM%'
     AND objects.type = 'V'
ORDER BY schemas.name, objects.name

You can now take that and iterate over them using a cursor.

 DECLARE db_cursor CURSOR FOR
  SELECT TOP 10 schemas.name + '.' + objects.name 'View'
    FROM sys.sql_modules
    JOIN sys.objects ON sql_modules.object_id=objects.object_id
    JOIN sys.schemas ON objects.schema_id=schemas.schema_id
   WHERE definition LIKE '%SELECT%*%FROM%'
     AND objects.type = 'V'
ORDER BY schemas.name, objects.name

DECLARE @View VARCHAR(128)
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @View
    
WHILE @@FETCH_STATUS = 0  
BEGIN
    EXEC sp_refreshview @View
    
    FETCH NEXT FROM db_cursor INTO @View
END

Upvotes: 0

JNK
JNK

Reputation: 65147

As Martin mentions, you need to refresh the view if you change the definition of the underlying table and use SELECT * in your view.

A really easy way to avoid this is to NEVER USE SELECT * in your views!

It's a terrible practice and can cause all sorts of issues. The only "benefit" is that you don't need to type out field names the ONE TIME you define the view.

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453028

You need to call sp_refreshview 'YourView' after altering the definition of tables selected from with *.

Upvotes: 9

Related Questions