CODEWITHSUNDEEP
sqlsql-serverview
dream
dream

Reputation: 95

In SQL Server, table add a column.　causing view error

In SQL Server, create a view using two tables. One of tables used * in view.

Then I add a column to the table. This is now causing view error.

Must rebuild the view to solve this problem.

If this table is used a lot of views, how to identify relevant views and rebuild them?

There are a number of ways?

Thank you!

Test environment: SQL Server 2008

Test SQL:

 if exists(select * from sys.objects where name='tblTestA' and type='u')
DROP TABLE  tblTestA

create table tblTestA(Part varchar(10),Qty int)
insert into tblTestA values('A',10)
insert into tblTestA values('B',20)
go

if exists(select * from sys.objects where name='tblTestB' and type='u')
    DROP TABLE  tblTestB
GO

create table tblTestB(Part varchar(10),Price decimal(9,4))
GO

insert into tblTestB values('A',1.1)
insert into tblTestB values('B',2.2)
GO

if exists(select * from sys.objects where name='v_test' and type='v')
    DROP VIEW  v_test
go

create View v_test
as
  select a.*,b.Price
  from tblTestA a, tblTestB b 
  where a.Part=b.Part
go

Execute:

select * from v_test 
go

result:

Part Qty Price
A   10  1.1000
B   20  2.2000

Add a column

alter table tblTestA add Remark nvarchar(200) not null default('test')
go

Execute:

select * from v_test 
go

Result:

Part Qty Price
A   10  test
B   20  test

Upvotes: 2

Views: 1400

Answers (3)

marc_s
marc_s

Reputation: 754488

We had the same problem - our solution has to come up with a SQL script to refresh all views. We run this script after every batch of update scripts that modify the database structure - this ensures that all views are up to date and functioning.

This is the T-SQL code:

DECLARE @viewname sysname
DECLARE @counter INT

SET @counter = 1

DECLARE viewname_cursor CURSOR FAST_FORWARD FOR
    SELECT v.[name] 
    FROM sys.views v
    INNER JOIN sys.sql_modules m ON v.object_id = m.object_id
    WHERE m.is_schema_bound = 0  -- only refresh non-schemabound views

OPEN viewname_cursor

FETCH NEXT FROM viewname_cursor INTO @viewname

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT CAST(@counter AS varchar(3)) + '. Refreshed view ' + @viewname

    EXEC sp_refreshview @viewname   -- refresh the view in question

    SET @counter = @counter + 1

    FETCH NEXT FROM viewname_cursor INTO @viewname
END

CLOSE viewname_cursor
DEALLOCATE viewname_cursor
GO

Upvotes: 2

James K. Lowden
James K. Lowden

Reputation: 7837

If you create your view WITH SCHEMABINDING, you won't encounter this problem. For your old views ;-) you can use sp_refreshview.

To find which views rely on which tables, you could use the GUI, but that doesn't scale very well. Instead, I recommend you use the system-management views e.g. sys.sql_expression_dependencies. That lets you iterate over the dependent views and issue exec sp_refreshview for each one.

Upvotes: 4

chue x
chue x

Reputation: 18803

You can figure out related views by using SQL Server Management Studio.

You need to select the table in SSMS and then right click it. Select View Dependencies.

In this picture the table is CompanyCategoryXref (blue arrow). The dependent view in this case is CompanyCategory (red arrow). You can also see that the selected item CompanyCategory is of object type View (yellow circle).

SSMS Dependency

Upvotes: 2

Related Questions