topic Re: Reading MS SQL metadata from my_DB.[INFORMATION_SCHEMA].[COLUMNS] ?? in SAS Programming

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

kjohnsonm — Tue, 19 Jul 2016 15:26:27 GMT

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.

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

Reeza — Tue, 19 Jul 2016 16:28:06 GMT

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

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

Reeza — Tue, 19 Jul 2016 16:29:56 GMT

Also for sashelp.vcolumns the entries are case sensitive.

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

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

kjohnsonm — Tue, 19 Jul 2016 17:25:22 GMT

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_%';

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

kjohnsonm — Tue, 19 Jul 2016 17:28:51 GMT

"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.

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

Reeza — Tue, 19 Jul 2016 17:46:34 GMT

See the output from the following.

Proc datasets library=libname;

run;quit;