get all tables in a SQL database using PROC SQL

[_LB]

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

[Peter_C]

you might need
SCHEMA=
to reveal names of tables

[Ksharp]

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


Ksharp

[jj030655]

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.

[niyiawe]

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;

[ChrisHemedinger · Accepted solution]

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.

[Babloo]

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;

[SASKiwi]

@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;