topic Re: get all tables in a SQL database using PROC SQL in SAS Programming

get all tables in a SQL database using PROC SQL

_LB — Tue, 24 May 2011 16:42:34 GMT

Hello all-
I am seeking to get all the table names from a SQL database. I am been searching on the web but have not found an adequate answer-

So far I have:

libname X oledb provider=sqloledb dsn='SQLXXXDR' properties=("Integrated Security"=SSPI "Persist Security Info"=True
"Initial Catalog"=medX) ;

This connects fine, but the problem I have is this-

PROC SQL;
SELECT *
FROM DICTIONARY.TABLES
WHERE UPCASE(LIBNAME)='X';
QUIT;

It tellsm e no rows are connected. Any help would be much appreciated.
Thanks,

Lawrence

Re: get all tables in a SQL database using PROC SQL

Peter_C — Tue, 24 May 2011 18:40:28 GMT

you might need
SCHEMA=
to reveal names of tables

Re: get all tables in a SQL database using PROC SQL

Ksharp — Mon, 30 May 2011 09:22:11 GMT

Just as Peter said. Do you check the X library by double-click .Did you see the table list in this X library?

Ksharp

Re: get all tables in a SQL database using PROC SQL

jj030655 — Wed, 01 Jun 2011 17:21:28 GMT

The lazy way...

libname sqldb "aldsjlkdfjlksjflkjfdlkasjf";

proc contents data=sqldb._all_ out=tablesAndColumns; run;

This will produce a dataset (tablesAndColumns) that looks like any SAS proc contents.

Re: get all tables in a SQL database using PROC SQL

niyiawe — Thu, 14 Sep 2017 20:13:58 GMT

PROC SQL;

SELECT *

FROM DICTIONARY.TABLES

WHERE UPCASE(LIBNAME) like 'the library name u are searching for'

AND UPCASE(MEMNAME) like 'if u remember one of the table name' ;

QUIT;

for example; the libname is ECOWAS and one of the table name is Nigeria

PROC SQL;

SELECT *

FROM DICTIONARY.TABLES

WHERE UPCASE(LIBNAME) like 'ECOWAS'

AND UPCASE(MEMNAME) like 'NIG%' ;

QUIT;

Re: get all tables in a SQL database using PROC SQL

ChrisHemedinger — Thu, 14 Sep 2017 20:22:25 GMT

I've read (but not verified/tested) that SQL Server supports the information_schema view that allows you to find all of the tables and attributes. If that's the case, you could use PROC SQL passthrough to CONNECT to the database and issue a query that brings that information back into SAS.

See this blog post for more info: Using PROC SQL to get the schema of a MySQL database. It's written for MySQL, but should be easy to adapt.

Re: get all tables in a SQL database using PROC SQL

Babloo — Fri, 07 Dec 2018 05:13:39 GMT

In similar fashion, could you please help me with the document to get attributes of DB2 tables from one schema in SAS? I tried the following code, but it is not working.

24         proc sql noprint;
25         	connect to db2 (datasrc=db2gtu
26         		authdomain=DB2AuthODS
27         		connection=global);
28         	create table work.status as
29         		(select * from
30               information_schema.tables
ERROR: Librefs are restricted to eight characters. information_schema has been truncated.
31             where table_schema="CREFIUS");
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
32         	disconnect from db2;
NOTE: Statement not executed due to NOEXEC option.
33         quit;

Re: get all tables in a SQL database using PROC SQL

SASKiwi — Fri, 07 Dec 2018 08:29:58 GMT

@Babloo - the post was about SQL Server and you are assuming the same technique will work in DB2? Working out how to do this in DB2 is as simple as googling "how to list tables in db2". That is how I found this useful link:

https://chartio.com/resources/tutorials/how-to-list-tables-in-ibm-db2/

Now you can try the recommended way like this:

proc sql noprint;
connect to db2 (datasrc=db2gtu authdomain=DB2AuthODS
connection=global);
create table DB_Tables as
select * from connection to db2
(SELECT
  *
FROM
  SYSIBM.SYSTABLES
WHERE
  type = 'T';
);
quit;