Read all table names from the database

[cho16]

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.

[Reeza]

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.

---

[ChrisHemedinger]

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;

[Reeza]

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.

[Tom]

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#p1f29m86u65hken1de...

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;

[s_lassen]

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;