An important question on views

Database
N/A

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
N/A

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
N/A

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