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,177

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

you might need
SCHEMA=
to reveal names of tables
Super User
Posts: 10,046

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.
Occasional Learner
Posts: 1

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

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;

Community Manager
Posts: 2,956

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

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.

Ask a Question
Discussion stats
  • 5 replies
  • 3946 views
  • 1 like
  • 6 in conversation