Reading MS SQL metadata from my_DB.[INFORMATION_SCHEMA].[COLUMNS] ??

[kjohnsonm]

Hello all,

Does anyone have a hint on how I can read just the table names in an entire MS SQL libname into sas?

For example:

proc sql;
create table temp as
SELECT TABLE_NAME
FROM my_lib.[INFORMATION_SCHEMA].[COLUMNS]
WHERE TABLE_NAME LIKE 'xw_%';
QUIT;

I have tried dropping the [], I have tried dropping ".[COLUMNS]"

I have tried making a temp table from the source MS SQL DB on this data, so I can just read a non-schema table directly like any other regular data set.   It shouldn't be that hard what am I missing?

last I have tried a proc

print data=sashelp.vcolumn noobs;

var memname name type [... etc];

where libname='my_lib';

run;

This last item seems to have hung my SAS app.  I can read and write to this libname for other tables.   TIA.

[Reeza]

Try a proc datasets though sometimes for DB it's all blanks. 

[kjohnsonm]

"Try a proc datasets though sometimes for DB it's all blanks." ---I am not sure what you mean buy this idea, however your other comment was spot on.

[Reeza]

See the output from the following. 

Proc datasets library=libname;

run;quit;

[Reeza · Accepted solution]

Also for sashelp.vcolumns the entries are case sensitive. 

Youve stated table names, is there a reason your not using vtable instead of vcolumn?

[kjohnsonm]

Thanks for the reminder. I forgot about case ...

proc print data=sashelp.vcolumn noobs;
where lower(libname)='my_lib';
run;

PS I also figured out the MS SQL side, the "table name" is MS's word sorry for that one, I did not fully translate the code from MS SQL to SAS.

SELECT distinct TABLE_NAME INTO my_xw_tables FROM [my_db].[INFORMATION_SCHEMA].[COLUMNS] where TABLE_NAME like 'xw_%';