topic Re: Read all table names from the database in SAS Procedures

Read all table names from the database

cho16 — Tue, 25 Sep 2018 19:50:40 GMT

Hi all,

I want to read all the table names from the database (dsn 'EDS').Currently I knew only 2 tables names to view all the data content information.

How can I pull all the table info from the database?

proc sql;

connect to odbc as DW (dsn="EDS" uid=dw pwd=dw);

create table hospmst as select * from connection to DW (select * from HOSPMaster);

create table hospdiv as select * from connection to DW (select * from HOSPdivisions);

quit;

Thanks in Advance.

Thanks.

Re: Read all table names from the database

Reeza — Tue, 25 Sep 2018 20:09:39 GMT

Most databases have some form of a dictionary table that lists all the tables and views. You may not have access to all the table and views but you should be able to query the table. Exactly how is dependent on your server, since you're using Pass-Through SQL. So you'll need to know what the underlying server is and the query that works in that particular language.

@cho16 wrote:

Hi all,

I want to read all the table names from the database (dsn 'EDS').Currently I knew only 2 tables names to view all the data content information.

How can I pull all the table info from the database?

proc sql;

connect to odbc as DW (dsn="EDS" uid=dw pwd=dw);

create table hospmst as select * from connection to DW (select * from HOSPMaster);

create table hospdiv as select * from connection to DW (select * from HOSPdivisions);

quit;

Thanks in Advance.

Thanks.

Re: Read all table names from the database

ChrisHemedinger — Tue, 25 Sep 2018 20:27:19 GMT

Building on the response from @Reeza, here's a technique that works for MySQL and a number of other databases.

Code excerpt:

/* must be run where SAS/ACCESS to MySQL available */
%let database=dbname; /* name of the MySQL database */
%let server = mysql.host.domain.com; /* your MySQL node */
 
proc sql;
  connect to mysql as source 
    (
    DATABASE=&database 
    SERVER=&server 
    PORT=3306 /* default port, change if needed */
    USER=your_user_id
    PASSWORD=secretPW
    );
  create table work.schematables as 
    select * from connection to source 
    (select * from 
      information_schema.tables 
    where table_schema="&database");
 
  create table work.schemacolumns as 
    select * from connection to source 
    (select * from 
      information_schema.columns 
    where table_schema="&database");
  disconnect from source;
 
quit;

Re: Read all table names from the database

Reeza — Tue, 25 Sep 2018 20:29:38 GMT

This all assumes you've done the obvious and asked your IT area if they have a list of data sets documented somewhere. For a full BI implementation that usually exists somewhere in some form. That's usually also helpful because it will have some business concepts attached to it.

Re: Read all table names from the database

Tom — Tue, 25 Sep 2018 21:19:53 GMT

Since you are using ODBC there is a special way to query that information.

http://support.sas.com/documentation/cdl/en/acreldb/65247/HTML/default/viewer.htm#p1f29m86u65hken1deqcybowtgma.htm

This example returns a list of the columns in the CUSTOMERS table.

proc sql;
   connect to odbc as mydb
      (datasrc="SQL Server" user=testuser password=testpass);
   select * from connection to mydb
      (ODBC::SQLColumns (, , "CUSTOMERS"));
quit;

Re: Read all table names from the database

s_lassen — Wed, 26 Sep 2018 11:28:12 GMT

Instead of using the special ODBC views to query the database, you can also assign it as a SAS libname:

libname dw odbc dsn='EDS' uid=dw pwd=dw schema=dbo;

(I assume that you are using the default schema, DBO, as your ODBC table names have no prefix).

You can then open the library dw using the Explorer window, or you can use dictionary.tables and dictionary.columns in SQL to see the contents.

Or you can copy all the stuff to WORK in one fell swoop:

proc copy in=dw out=Work mt=data;
run;