2

Using SQL Server Express 2008 & Management Studio.

I have a table & a view. At the time the View was created, the Table's FirstName column was varchar(50). Modify Table.Firstname to be nvarchar(50) and the View still indicates that the column is varchar(50).

How do I get the View to display the current column definition?

Here's what I've tried (that didn't work):

  • Click the Refresh button
  • Close Management Studio & re-open it.

Here's what did work:

  • Create a brand new view. The new view does display the current field definition

Note: This project has many views, so I'd prefer not to have to re-create all of them.

Lorraine
  • 1,189
  • 14
  • 30
Mark Maslar
  • 1,121
  • 4
  • 16
  • 28

2 Answers2

4

Run either sp_refreshview or the newer sp_refreshsqlmodule

The best solution is to use WITH SCHEMABINDING so this can't happen.

Quite common on SO, example here

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
0

Look up sys.sp_refreshsqlmodule in Books Online.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • Thanks! In this particular case, sp_refreshsqlmodule works well for views that were created w/o "WITH SCHEMABINDING". e.g. EXEC sys.sp_refreshsqlmodule 'myDatabase.dbo.myView1' – Mark Maslar Dec 09 '10 at 21:27