删除表后刷新视图 [英] Refresh View after table drop

问题描述

我有以下表格:

系列一表:

create table tbl1
(
id int,
name varchar(100)
);
insert into tbl1 values(1,'tbl1');

create table tbl2
(
id int,
name varchar(100)
);
insert into tbl2 values(1,'tbl2');

create table tbl3
(
id int,
name varchar(100)
);
insert into tbl3 values(1,'tbl3');

create table tbl4
(
id int,
name varchar(100)
);
insert into tbl4 values(1,'tbl4');

系列双人桌:

create table tbl11
(
id int,
name varchar(100)
);
insert into tbl11 values(1,'tbl11');

create table tbl22
(
id int,
name varchar(100)
);
insert into tbl22 values(1,'tb22');

create table tbl33
(
id int,
name varchar(100)
);
insert into tbl33 values(1,'tbl33');

create table tbl44
(
id int,
name varchar(100)
);
insert into tbl44 values(1,'tbl44');

现在我想创建每个系列表的VIEW:

Now I want to create VIEW of each series tables:

系列一视图:

create view View_tbl_one_series as 
select * from tbl1
union all
select * from tbl2
union all
select * from tbl3
union all
select * from tbl4

系列双视图:

create view View_tbl_double_series as 
select * from tbl11
union all
select * from tbl22
union all
select * from tbl33
union all
select * from tbl44

之后我出于某种原因DROP TABLE tbl1并创建了另一个包含两个系列视图定义的VIEW.

After that I DROP TABLE tbl1 for some reason and creating another VIEW which contains the definition of two series views.

查看全部:

create view All_VIEW AS
select * from View_tbl_one_series
union all
select * from View_tbl_double_series

出现错误:

消息 208，级别 16，状态 1，过程 View_tbl_one_series，第 2 行无效的对象名称tbl1".

Msg 208, Level 16, State 1, Procedure View_tbl_one_series, Line 2 Invalid object name 'tbl1'.

尝试:

exec sp_refreshview View_tbl_one_series

但仍然出现同样的错误.

but still getting same error.

注意:我在数据库系统中有很多tables和views，创建view all是最后一个过程，中间有出于某种原因删除一些表.

Note: I have many tables and views in the database system, and creating view all is the last procedure, and between that have to drop some tables for some reason.

推荐答案

SQL Server 中的非物化视图可以被认为只是出现在该视图中的基础表之上的一个薄包装器.如果删除视图中涉及的一个或多个表，它将不起作用，因为无法再查询该表.这里有许多解决方法，其中之一就是创建一个索引(物化)视图:

A non materialized view in SQL Server can be thought of as just a thin wrapper on top of the underlying tables which appear in that view. If you drop one or more of the tables involved in the view, it won't work, because the table can no longer be queried. There are a number of workarounds here, one of which would be to just create an indexed (materialized) view:

CREATE VIEW View_tbl_one_series
WITH SCHEMABINDING
AS 
SELECT * from tbl1
UNION ALL
SELECT * from tbl2
UNION ALL
SELECT * from tbl3
UNION ALL
SELECT * from tbl4
GO
CREATE UNIQUE CLUSTERED INDEX idx ON View_tbl_one_series (id);

其他选项包括出于相同目的使用临时表，或者甚至是真正的常规表.

Other options would include using a temporary table for the same purpose, or maybe even a bona fide regular table.

请注意，通常在联合查询中执行 SELECT * 并不理想，因为它留下了列/列类型可能无法在联合中涉及的两个表之间正确对齐的可能性.

Note that in general doing SELECT * in a union query is not ideal, because it leaves open the possibility that the columns/column types may not line up properly between the two tables involved in the union.

这篇关于删除表后刷新视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！