An important question on views

Database
Enthusiast

An important question on views

Views represent tables. IS there any way to find the name of view for which the base table DDL has changed.
A simple method is to run the view, if it throws a syntax error, then we can know that the base table DDL has changed.But this is quite a manual task, much so when we have to check for 100s of view.
Any better suggestion friends?
2 REPLIES
Enthusiast

Re: An important question on views

The following bteq script will set up a table with all the views which showed any error when they were accessed.

Replace Macro DuffView (DBName Char(30) Not Null, ViewName Char(30) Not Null)
AS
(Insert Into DBName.Views_To_Check
Values (':DBName',':ViewName', Current_Date)
;

.Set FoldLine On 1
.Export Report File = 'FName'
.Heading ''
.Width 250

Select
'Select Top 1 * From '||Trim(DatabaseName)||
'.'||Trim(TableName)||' ;'
(Title)
, '.If ErrorCode <> 0 Then Exec DuffView ('''||Trim(Databasename)||
''','''||Trim(Tablename)||''');'
(Title '')
From DBC.Tables
Where Databasename = 'dbtocheck'
And Tablekind = 'V'
Order By Databasename, Tablename
;
.If ErrorCode <> 0 Then .Quit 8

.Export Reset

.Run File = FName

.Quit
Enthusiast

Re: An important question on views

Slight typo when I pasted the wrog version in!

That should be:

Replace Macro DuffView (DBName Char(30) Not Null, ViewName Char(30) Not Null)
AS
(Insert Into DBName.Views_To_Check
Values (':DBName',':ViewName', Current_Date)
;

.Set FoldLine On 1
.Export Report File = 'FName'
.Heading ''
.Width 250

Select
'Select Top 1 * From '||Trim(DatabaseName)||
'.'||Trim(TableName)||' ;'
(Title '')
, '.If ErrorCode <> 0 Then Exec DuffView ('''||Trim(Databasename)||
''','''||Trim(Tablename)||''');'
(Title '')
From DBC.Tables
Where Databasename = 'dbtocheck'
And Tablekind = 'V'
Order By Databasename, Tablename
;
.If ErrorCode <> 0 Then .Quit 8

.Export Reset

.Run File = FName

.Quit