check when tera view table was lastly updated

[Ronein]

Hello

Lets say that I want to check when a teadata view table was lastly updated.

If I run the following query in teradata then I get the answer 

Select TableName , CreatorName, CreateTimeStamp, LastAlterName, LastAlterTimeStamp, AccessCount, LastAccessTimeStamp
from DBC.Tables  
Where upper(DataBaseName) = 'DWP_VALL'
And TAblename= 'VBM374_USED_BRANCH_CUSTOMER'

My question- 

Lets say that I want to work on sas and run the query in  sas .

If I run this query then it is working so I know that this connector is working well

proc sql;  
connect to teradata (server=dwprod schema=DWP_vall authdomain=TeraDataAuth);
create table VBM374_USED_BRANCH_CUSTOMER  as 
select * from connection to teradata
(
select top 10 *
from VBM374_USED_BRANCH_CUSTOMER
);
disconnect from teradata;
quit ;

But if i run  the query that check last time updated via sas then i get error

ERROR: At least one of the columns in this DBMS table has a datatype that is not supported by this engine.

proc sql;  
connect to teradata (server=dwprod schema=DWP_vall authdomain=TeraDataAuth);
create table WANT  as 
select * from connection to teradata
(
Select TableName , CreatorName, CreateTimeStamp, LastAlterName, LastAlterTimeStamp, AccessCount, LastAccessTimeStamp
from DBC.Tables  
Where upper(DataBaseName) = 'DWP_VALL'
And TAblename= 'VBM374_USED_BRANCH_CUSTOMER'
);
disconnect from teradata;
quit ;

Here is full  log of error

1                                                          The SAS System                         09:02 Saturday, September 20, 2025

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROCESSFLOWNAME='Standalone Not In Project';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=SVG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         %macro HTML5AccessibleGraphSupported;
15             %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16         %mend;
17         FILENAME EGHTML TEMP;
18         ODS HTML5(ID=EGHTML) FILE=EGHTML
19             OPTIONS(BITMAP_MODE='INLINE')
20             %HTML5AccessibleGraphSupported
NOTE: The ACCESSIBLE_GRAPH option is pre-production for this release.
21             ENCODING='utf-8'
22             STYLE=HTMLBlue
23             NOGTITLE
24             NOGFOOTNOTE
25             GPATH=&sasworklocation
26         ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27         
28         
29         proc sql;
30         connect to teradata (server=dwprod schema=DWP_vall authdomain=TeraDataAuth);
31         create table WANT  as
32         select * from connection to teradata
33         (
34         Select TableName , CreatorName, CreateTimeStamp, LastAlterName, LastAlterTimeStamp, AccessCount, LastAccessTimeStamp
35         from DBC.Tables
36         Where upper(DataBaseName) = 'DWP_VALL'
37         And TAblename= 'VBM374_USED_BRANCH_CUSTOMER'
38         );
ERROR: At least one of the columns in this DBMS table has a datatype that is not supported by this engine.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
39         disconnect from teradata;
NOTE: Statement not executed due to NOEXEC option.
40         quit ;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.53 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              204.84k
      OS Memory           24472.00k
      Timestamp           09/21/2025 10:02:33 AM
      Step Count                        21  Switch Count  18
      Page Faults                       0
      Page Reclaims                     34
      Page Swaps                        0
      Voluntary Context Switches        125
2                                                          The SAS System                         09:02 Saturday, September 20, 2025

      Involuntary Context Switches      2
      Block Input Operations            0
      Block Output Operations           0
      
41         
42         
43         %LET _CLIENTTASKLABEL=;
44         %LET _CLIENTPROCESSFLOWNAME=;
45         %LET _CLIENTPROJECTPATH=;
46         %LET _CLIENTPROJECTPATHHOST=;
47         %LET _CLIENTPROJECTNAME=;
48         %LET _SASPROGRAMFILE=;
49         %LET _SASPROGRAMFILEHOST=;
50         
51         ;*';*";*/;quit;run;
52         ODS _ALL_ CLOSE;
53         
54         
55         QUIT; RUN;
56         

[Patrick]

Based on some Copilot Q&A please try below and let us know if this works for you.

Select 
  TableName, 
  CreatorName, 
  CAST(CreateTimeStamp AS VARCHAR(26)) AS CreateTimeStamp,
  LastAlterName, 
  CAST(LastAlterTimeStamp AS VARCHAR(26)) AS LastAlterTimeStamp,
  AccessCount, 
  CAST(LastAccessTimeStamp AS VARCHAR(26)) AS LastAccessTimeStamp
from DBC.Tables
Where upper(DataBaseName) = 'DWP_VALL'
  And TAblename = 'VBM374_USED_BRANCH_CUSTOMER'

[Tom]

Since you are only pulling over some of the columns it should not be that hard to use elimination to determine which one SAS considers as using a data type it does not support.

But I wonder what you mean by creating time of a VIEW?  And why you want to know it?

The last time a view is modified would be the last time the view's definition was changed.  Is that what you need to know?  If you want to know the last time the DATA that the view might surface was changed you probably will need to check the modification times of the TABLES that the view queries.