Troubleshooting: Meta-information updating issue for non-schemabound views

LINKS

General

Apex SQL Audit
Apex SQL Clean
Apex SQL Code
Apex SQL Diff
Apex SQL Diff (Data)
Apex SQL Doc
Apex SQL Edit
Apex SQL Enforce
Apex SQL Log
Apex SQL Report
Apex SQL Script

Troubleshooting Home

SITE FEED

Site Feed

Support Forum

The fastest and most effective to get answers to your questions quickly. Click Here for more info.

FAQs

Quick answers to common questions. Click Here for more info.

Troubleshooting

Known issues and basic troubleshooting techniques for problems or unexpected behaviors. Click Here for more info.

Knowledgebase

Technical tips, How-to articles, and other tutorials about ApexSQL Tools. Click Here for more info.

Online Documentation

All of our help files for download or convenient viewing online. Click Here for more info.

Videos

Watch product demos, training videos, or tutorials of our products' main features. We frequently upload new videos so check often for updates. Click Here for more info.

Troubleshooting

Troubleshooting and Known issues with Apex SQL Tools

Meta-information updating issue for non-schemabound views

SUMMARY

The meta-information of non-schemabound view may corrupt after synchronizing of underlying tables.

SYMPTOMS

If the view is based on the query of a following type:


SELECT *

FROM   TABLE1

and Table1 has changed its schema during structure synchronization then meta-information of the view doesn't reflect the changes of the underlying table structure.

CAUSE

The view meta-information doesn't updated automatically after synchronizing the structure of the tables the view depends on.

RESOLUTION

The following query can be used after table Table1 is synchronized to update all non-schema-bound views that are dependent on it:


SELECT DISTINCT 'EXEC sp_refreshview ''' + NAME + ''''

FROM   SYS.OBJECTS SO

       INNER JOIN SYS.SQL_DEPENDENCIES SD

         ON SO.OBJECT_ID = SD.OBJECT_ID

WHERE  (TYPE = 'V'

        AND SD.REFERENCED_MAJOR_ID = OBJECT_ID('dbo.Table1')

        AND OBJECTPROPERTY(OBJECT_ID(NAME),'IsSchemaBound') = 0)

LAST REVIEW

12 September 2007 Date of Last Review

Labels: ApexSQL Diff

posted by Irene Sanford on Tuesday, July 03, 2007

0 Comments: