List of dataset names in a library is differ from the list based on a code

[L2Fly]

Hello - I am on version of 7.3 and using EG writing a code to get list of registered tables/views from a specific library.  All of our tables and views are based on SQL Server DB. 

I have found this code

LIBNAME LIBPROD ODBC DATAsrc=SQLODBC SCHEMA=dbo ;

ods output Members=Members;

proc datasets library=LIBPROD memtype=data;

run;

quit;

when I run this code, it returns over 450 tables and views but when I look at my library from EG, I have a list of about 80 tables and views. 

What I want to do is to get a list of the registered tables (the ones I can see from EG when I open the library) and also like to know why I am getting this huge list of tables and views? 

Thanks for your help.

[SuryaKiran]

Query dictionary.tables to find the tables in that particular library. 

proc sql;
select * 
from dictionary.tables
where libname="LIBPROD" ;
quit;

Not sure why there is a difference in the count, try changind the dbo to upper case and see if that's causing the issue

[Reeza]

I believe this varies based on how your data is set up. PROC DATASETS and the SAS dictionary tables DO NOT have metadata for data on the servers. Unless it's set up as a SAS server. You can usually get this information via SQL pass through, or otherwise you have to rely on your institutional documentation which can often be lacking.

[L2Fly]

thank you for your inputs.  From SMC I do right click on the library and then select Advanced Options in Options tab.  I selected the check box for Library is pre-assigned and then selected by Metadata Engine and still is the same issue.  I changed the lower case dbo to DBO and didn't make any changes.

Still don't know why the list under library is so different than the code I provided earlier!  The second set of code that was provided in reply to my questions came with the same exact results!!!

[Reeza]

Are the table names longer than 32 characters?

[L2Fly]

Thank you for your response, no tables are not longer than 32.  What do you mean by "List of dataset names in a library is differ from the list based on a code"

[Reeza]

That’s your question....not my response.

[SASKiwi]

Is this library also defined in SAS metadata and you are registering the tables in SAS metadata? If so, EG will only show registered tables in the EG server libraries window and that may explain the difference.

[CaseySmith]

I agree with @SASKiwi...  it sounds like your library is assigned via the META engine (MLE) and using METAOUT=ALL (the default), thus "The user is restricted to only the tables and columns that have been defined in the repository."  When your library is assigned this way, only tables registered in metadata will be visible in the library in EG.  Physical tables that exist in that physical location but are not registered in metadata will not appear in the library.  If you want unregistered physical tables to also appear, you can use the METAOUT=DATA option on the META libname statement, or use a different libname engine to assign the library.

Here is documentation on the METAOUT option.

Casey

[L2Fly]

Thank you @CaseySmith and all others.  Initially I want to get the list of only tables registered in metadata the ones that only visible from the Library in EG.  I am looking for a code for that!

Now after founding out that with the code above I am able to see list of all of our SQL tables and views it makes me wondering if anyone could access them even if they are not registered in metadata?  If they can then I have a security issue that I need to fix. 

I have some users using EG to access the registered tables and these tables/views are carefully been selected and registered to SAS.  Now if someone could access other tables and views it would be a very bad thing.  How can I check?  We are very new into SAS and as we are finding some "loop-holes" we try to fix them.

Thanks again

[SASKiwi]

Running a LIBNAME statement in EG will completely bypass any restrictions you have set up in SAS metadata for the same data source. If you don't want this to happen you have two choices:

1. Control database access in the external database itself. This is the best option as it covers all database accessing not just SAS. Your DBA should be able to advise you on this.
2. Implement SAS metadata-bound libraries in which case any LIBNAME has to obey the metadata rules. I would be very careful about choosing this option as it is not a trivial change and requires extra administration.

You can read up about metadata-bound libraries here: https://documentation.sas.com/?docsetId=seclibag&docsetTarget=bookinfo.htm&docsetVersion=9.4&locale=...

[Patrick]

I agree with what @SASKiwi and @CaseySmith write. It's most likely due to a libname using the meta engine with options only returning tables registered in SAS Metadata.

If you want to get the same result in your query than what you see in SAS EG then you need to use the exactly same libref (same libname statement) to retrieve the list of tables.

[L2Fly]

Thanks again but I am using the exact lib name "LIBPROD"!

LIBNAME LIBPROD ODBC  DATAsrc=EISPROD  SCHEMA=dbo ;

[SASKiwi]

My understanding is that you have a LIBPROD library defined in SAS metadata which you would have set up in SAS Management Console. Then you also assign the same library with a LIBNAME statement in code. Is this correct? If not please explain.

If this is correct, then the metadata-defined library will only show metadata-registered tables, by default, but the coded LIBNAME statement ignores metadata-registered tables and shows you directly what is in the database. This appears to explain the behaviour you are seeing.

[Tom]

---

@L2Fly wrote:

Thanks again but I am using the exact lib name "LIBPROD"!

LIBNAME LIBPROD ODBC  DATAsrc=EISPROD  SCHEMA=dbo ;

---

So you don't want to use metadata server to connect?

That LIBNAME statement will close any existing libref named LIBPROD and create a new libref using the ODBC engine to connect directly and by-pass the metadata server.

If LIBPROD is setup automatically why are you trying to change it?

If LIBPROD is not setup automatically then you need to use the same syntax for creating it that EG used to create it.  Try using the LIST option to see how it is defined in your EG session.

libname libprod list;