Find the list of tables, list of columns, and column attributes from the database using the ODBC engine and PROC SQL Pass-Through


You can use an ODBC query of ODBC::SQLTables to get a list of tables, and the query ODBC::SQLColumns to get the column information from a specific table from the ODBC data source.

In the following example, data set LIST1 contains a list of all the tables in the ODBC data source, data set LIST2 is the column information from the table DEPT and data set LIST3 is the information about just one column, DNAME.

proc sql;
connect to odbc(dsn=odbc_data_source_name uid=user_ID pwd=XXXXX);
create table list1
as
select * from connection to odbc(ODBC::SQLTables);

create table list2
as
select * from connection to odbc(ODBC::SQLColumns,,"DEPT",);

create table list3
as
select * from connection to odbc(ODBC::SQLColumns,,"DEPT","DNAME");
quit;