Refreshing View Meta-Data

When database schema changes, as it often does during upgrades or customizations, any existing views refering to that schema remain unaltered.  For example, if a UserId field is lengthened from 30 to 60 characters in the schema, any view referencing that field will remain at 30 characters.  The solution to this, short of editing all the views, is to call sp_refreshview on the view.

The following is a little script that can be run on your database.  It will refresh the views for all schema objects in the 'dbo' schema (which includes all IMIS tables).  If you have a lot of tables in your database that exist in other schemas, you may wish to eliminate the clause that limits the script to only 'dbo', in order to ensure everything is refreshed and up to date.  This can be run at any time, and it is completely non-destructive.  If this script throws any errors, it's likely due to the existence of old obsolete views that reference schema that has either been renamed or dropped... you'll have to manually fix such issues by either fixing or dropping the offending view.

-- Refresh the metadata for all views
-- in the 'dbo' schema
DECLARE @viewName AS VARCHAR(255)

DECLARE listOfViews CURSOR FOR
    SELECT [TABLE_NAME]
      FROM INFORMATION_SCHEMA.VIEWS
     WHERE [TABLE_SCHEMA] = 'dbo'
     ORDER BY [TABLE_NAME]

OPEN listOfViews
FETCH NEXT FROM listOfViews into @viewName

    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
        EXEC sp_refreshview @viewName
        FETCH NEXT FROM listOfViews INTO @viewName
    END

CLOSE listOfViews
DEALLOCATE listOfViews

Running this on your databases and fixing any errors is a very good idea.  Of course, you should test it out on an off-line backup copy of any live or production databases first, so you can get an idea of what it does, and what problems may exist, and then plan for how to fix them.