How to Retrieve SQL table_names, Column_names from SQL server with SAS/ACCESS OLE DB server

pavanpragada0 — Mon, 26 Oct 2020 13:02:58 GMT

Hi Guys,

Can some one help me to retrieve the table name & column name data from SQL server DB

I am trying to work with following code, can some one correct me .

Thanks in Advance!!

libname mydb oledb
provider=sqloledb
datasource=myservername
properties=( "initial catalog"=mydbname )
prompt=yes
schema=myschemaname
;

Proc sql;

create detailsTN as(select * from connect to mydb (select Table_name from information_schema.tables););

create detailsCN as (select * from connect to mydb (select Column_name from information_schema.tables););

quit;

Re: How to Retrieve SQL table_names, Column_names from SQL server with SAS/ACCESS OLE DB server

LinusH — Tue, 27 Oct 2020 09:32:42 GMT

You should be able to use information_schema to retrieve this information.

Also, once you have a libname assigned, you should be able to use SASHELP views and PROC SQL DICTIONARY tables.