SQL 如何在 ALTER TABLE 后正确运行 ALTER VIEW
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8874533/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to properly run ALTER VIEW after ALTER TABLE
提问by Jason
Lets say I have this SQL statements:
假设我有这个 SQL 语句：
ALTER TABLE dbo.[tbl] ALTER COLUMN col1 varchar(300)
ALTER TABLE dbo.[tbl] ALTER COLUMN col2 varchar(20)
ALTER TABLE dbo.[tbl] ALTER COLUMN col3 varchar(20)
ALTER TABLE dbo.[tbl] ALTER COLUMN col4 varchar(100)
I have put these statements inside a file to be run one after the other. I also have a VIEW that is looking at dbo.[tbl]. I noticed that after running the 4 statements above the VIEW retains the old column lengths. So I added the following code to the end of the file:
我将这些语句放在一个文件中，一个接一个地运行。我还有一个查看 dbo.[tbl] 的 VIEW。我注意到在运行上面的 4 条语句后，VIEW 保留了旧的列长度。所以我在文件末尾添加了以下代码：
ALTER VIEW [dbo].[tbl]
AS
SELECT col1, col2, col3, col4
FROM dbo.[tbl]
The error that I get is
我得到的错误是
'ALTER VIEW' must be the first statement in a query batch
So my question is, what is the best way to ensure that my VIEW retains the new column lengths?
所以我的问题是，确保我的 VIEW 保留新列长度的最佳方法是什么？
回答by GSerg
For this specific purpose, use sp_refreshview.
为此特定目的，请使用sp_refreshview.
exec sp_refreshview N'dbo.tbl'
回答by Holistic Developer
Use GOstatements:
使用GO语句：
ALTER TABLE dbo.[tbl] ALTER COLUMN col1 varchar(300)
ALTER TABLE dbo.[tbl] ALTER COLUMN col2 varchar(20)
ALTER TABLE dbo.[tbl] ALTER COLUMN col3 varchar(20)
ALTER TABLE dbo.[tbl] ALTER COLUMN col4 varchar(100)
GO
ALTER VIEW [dbo].[tbl]
AS
SELECT col1, col2, col3, col4
FROM dbo.[tbl]
GO
