Sample 25965: Find a list of tables, list of columns and column attributes from a database using ODBC engine and PROC SQL Pass-Through
When using libname statment with SAS/ACCESS to ODBC, SAS Explorer window may not display all the tables. Proc Sql pass-thorugh can be used to get a list of tables from a data base such as Sql Server, Oracle, Sybase, DB2 or Informix. This code can be used on Windows or UNIX.
An ODBC query ODBC::SQLTables can be used to get a list of tables
and ODBC::SQLColumns can be used to get the column information from a specific table from the ODBC data source.
In this example, data set LIST1 returns a list of all the
tables in the ODBC data source, data set LIST2 returns a list of the columns from the table DEPT and data set LIST3 is the information about just one column, DNAME.
This code will not run stand-alone since it requires data base connection information.
dsn="data source name that you are connecting"
uid=database userid
pwd=database password
proc sql;
connect to odbc(dsn=sqlserver uid=dbitest pwd=dbigrp1);
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;
You can find out a list of tables, columns and column attributes from a database using PROC SQL Pass-Through and ODBC queries
| Type: | Sample |
| Topic: | Data Management ==> Data Sources ==> External Databases ==> DB2
Third Party ==> Information Exchange ==> ODBC (Open Database Connectivity)
Data Management ==> Data Sources ==> External Databases ==> MS SQL Server
Data Management ==> Data Sources ==> External Databases ==> MySQL
SAS Reference ==> Statements ==> Data Access ==> LIBNAME
Data Management ==> Data Sources ==> External Databases ==> Dbase
SAS Reference ==> LIBNAME Engines
SAS Reference ==> Procedures ==> SQL
|
| Date Modified: | 2005-08-20 03:02:37 |
| Date Created: | 2005-08-11 09:37:42 |
Operating System and Release Information
| SAS System | SAS/ACCESS Interface to ODBC | AIX | 8.2 TS2M0 | n/a |
| HP-UX | 8.2 TS2M0 | n/a |
| Linux | 8.2 TS2M0 | n/a |
| Solaris | 8.2 TS2M0 | n/a |
