CODEWITHSUNDEEP
sqlsql-server-2008sql-server-2005t-sql
Jason
Jason

Reputation: 551

How to properly run ALTER VIEW after ALTER TABLE

Lets say I have this SQL statements:

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:

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?

Upvotes: 13

Views: 38002

Answers (2)

GSerg
GSerg

Reputation: 78155

For this specific purpose, use sp_refreshview.

exec sp_refreshview N'dbo.tbl'

Upvotes: 21

Holistic Developer
Holistic Developer

Reputation: 2526

Use GO statements:

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

Upvotes: 11

Related Questions