Check if table exists in database

[Patrick]

---

@Tom wrote:

Not sure how much the results from using SAS/Access to Oracle to access an Oracle database translate into using SAS/Access to ODBC to connect to a Teradata database.

---

It might not - but @Reeza statement was for "DB tables" in general and for this the Oracle engine appeared good enough to me.

Also: If the information doesn't make it into the dictionary tables then how comes we can normally see such tables in UI's like SAS EG under the library node. So I'm rather guessing that if DB tables aren't available in the SAS dictionary then there are some DB permissions "missing" site specific which prohibit the SAS user to query the DB metadata. 

[Reeza]

It may depend on if your system registered the tables, but historically any time I would connect to DB2, MS SQL DB the table names would not show in the dictionary.table.

It may vary based on the connection type (ODBC vs DB Specific drivers)??

It definitely does not work for all DB library so not sure what the rules are there.

[avellani]

This worked great for me.
And instead of this:

%else %do;
%put Could not find &dsname ;
%end;

I created an empty global temp table:
%else %do;
/* If Oracle table does not exists, create it */
proc sql ;
connect to oracle (path="hcemi" user=&hcemi_u. password="%superq(hcemi_p)"
connection=shared adjust_byte_semantic_column_lengths=yes);
execute (create global temporary table "%sysuserid..temptrans (mbr_id char(46))) by oracle ;
quit ;
%end ;

Thanks!

[Tom]

What I had to do to check if a Teradata table or view existed was query the Teradata metadata directly instead.

Here is part of a macro I made:

*----------------------------------------------------------------------------;
* Check if table or view exists ;
*----------------------------------------------------------------------------;
select obj into :result trimmed from connection to &connection
  (select case when (tablekind in ('T','O')) then 'TABLE'
          else 'VIEW' end as obj
    from dbc.tablesv
    where databasename = %squote(&db) and tablename= %squote(&table)
      and tablekind in ('V','T','O')
  )
;
%let exists=&sqlobs;

I also included code to check if there is any Volatile table with that name.

%if (^&exists and &db=&userid) %then %do;
*----------------------------------------------------------------------------;
* Check for any Volatile tables ;
*----------------------------------------------------------------------------;
  select 1 into :exists from connection to &connection (help volatile table) ;

  %if (&exists) %then %do;
*----------------------------------------------------------------------------;
* Check if this Volatile table exists ;
*----------------------------------------------------------------------------;
    select 'VOLATILE TABLE' into :result
      from connection to &connection (help volatile table)
%*----------------------------------------------------------------------------
Set VARNAME based on VALIDVARNAME setting.
-----------------------------------------------------------------------------;
%if %sysfunc(getoption(validvarname))=ANY %then
      where upcase('table name'n) = "&table"
;%else
      where upcase(table_name) = "&table"
;
    ;
    %let exists=&sqlobs;
  %end;
%end;