BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
_LB
Fluorite | Level 6 _LB
Fluorite | Level 6
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
1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHemedinger
Community Manager

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.

Check out SAS Innovate on-demand content! Watch the main stage sessions, keynotes, and over 20 technical breakout sessions!

View solution in original post

7 REPLIES 7
Peter_C
Rhodochrosite | Level 12
you might need
SCHEMA=
to reveal names of tables
Ksharp
Super User
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
Calcite | Level 5
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
Calcite | Level 5

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
Community Manager

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.

Check out SAS Innovate on-demand content! Watch the main stage sessions, keynotes, and over 20 technical breakout sessions!
Babloo
Rhodochrosite | Level 12

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
PROC Star

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

 

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 33208 views
  • 2 likes
  • 8 in conversation