DATA Step, Macro, Functions and more

get all tables in a SQL database using PROC SQL

Reply
Regular Contributor
Regular Contributor
Posts: 170

get all tables in a SQL database using PROC SQL

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
Valued Guide
Posts: 2,174

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

you might need
SCHEMA=
to reveal names of tables
Super User
Posts: 9,671

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

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


Ksharp
Occasional Contributor
Posts: 9

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

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.
Ask a Question
Discussion stats
  • 3 replies
  • 3288 views
  • 0 likes
  • 4 in conversation