Report of Table names in a library

[breelloyd]

Hi Guys,

I'm stuck as far as to why one of my tables (from my SQL Database) isn't coming through into my library. Almost everything else is. I'm looking to QA it. Try and work out if there are any other tables that are missing.

So my question: how can I produce a list of all the tables that are in a SAS library that I've created?

Thanks,

Bree

[Reeza · Accepted solution]

Try proc datasets.

check how long the table name is, if its over 32 characters it won't show up.

[breelloyd]

it's over 32 chars long!

Is there a work around for this? or do I need to go back to the IT team?

[SASKiwi]

One workaround would be to query the "dictionary" tables of the source SQL database. Which SQL database are you using, for example Oracle, SQL Server, as each database names its dictionary tables differently.

[ChrisHemedinger]

If you want to see what's in the database (and not just what you see in a SAS library), try this technique:

Using PROC SQL to get the schema of a MySQL database - The SAS Dummy

Even though the article is about MySQL, it can apply to other databases as well.

Chris

[Haikuo]

Hi Chris,

Information schema is not supported by Oracle?

I have been using something like ALL_VIEWS, ALL_TABLES, ALL_TAB_COLUMNS to query Oracle Metadata.

Haikuo

[ChrisHemedinger]

Yes, different databases have different schema mechanisms.  My example works with MySQL and a few others.  I just wanted to point out that you can use PROC SQL to pass a database-specific request to get information as needed.

Chris

[Reeza]

You will be able to access the table using SQL Pass Thru or you can ask IT to change the name to less than 32 characters. It depends on what your needs are, what the best solution would be.

[breelloyd]

It looks like the boys are going to build a DB for me, Basically views so I can access all the data, and we'll rename everything.

The same goes for Field names right? If I have a table with a field name that is >32 Char, what happens? does it just neglect to import that field or does it cause the whole table import to fail? We have plenty of Table names >32 Char.