DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 90
Accepted Solution

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

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.


Accepted Solutions
Solution
‎07-19-2016 01:25 PM
Super User
Posts: 19,855

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

Posted in reply to kjohnsonm

Also for sashelp.vcolumns the entries are case sensitive. 

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

View solution in original post


All Replies
Super User
Posts: 19,855

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

Posted in reply to kjohnsonm

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

Frequent Contributor
Posts: 90

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

"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.
Super User
Posts: 19,855

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

Posted in reply to kjohnsonm

See the output from the following. 

 

Proc datasets library=libname;

run;quit;

 

 

Solution
‎07-19-2016 01:25 PM
Super User
Posts: 19,855

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

Posted in reply to kjohnsonm

Also for sashelp.vcolumns the entries are case sensitive. 

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

Frequent Contributor
Posts: 90

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

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_%';
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 358 views
  • 2 likes
  • 2 in conversation