New question on previous topic: PROC CONTENTS of entire library with all variables detail

[AndreaMacLeod]

I am trying to use the previously posted Solution from 'PROC CONTENTS of entire library with all variables detail': https://communities.sas.com/t5/SAS-Procedures/PROC-CONTENTS-of-entire-library-with-all-variables-det... without success.

Am I missing a step or required setting to the recommended code?

First code/log:

LIBNAME dawn SQLSVR datasrc=SQL404 user="&dawn_user" pw="&dawn_pw";

proc sql;
create table columns as
select name as variable
,memname as table_name
from dictionary.columns
where libname = 'DAWN'
;
quit;

NOTE: Libref DAWN was successfully assigned as follows:
Engine: SQLSVR
Physical Name: SQL404
28
29 /*_______________________________________________________*/
30
31 proc sql;
32 create table columns as
33 select name as variable
34 ,memname as table_name
35 from dictionary.columns
36 where libname = 'DAWN'
37 ;
NOTE: Table WORK.COLUMNS created, with 0 rows and 2 columns.
38 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.06 seconds
cpu time 0.03 seconds

Second code/log:

PROC DATASETS LIB = DAWN;
CONTENTS DATA =_all_; 
QUIT; 
RUN;

27 PROC DATASETS LIB = DAWN;
WARNING: No matching members in directory.
28 CONTENTS DATA =_all_;
29 QUIT;

WARNING: No matching members in directory.
NOTE: PROCEDURE DATASETS used (Total process time):
real time 0.06 seconds
cpu time 0.00 seconds

30 RUN;
31
32 GOPTIONS NOACCESSIBLE;
33 %LET _CLIENTTASKLABEL=;
34 %LET _CLIENTPROCESSFLOWNAME=;
35 %LET _CLIENTPROJECTPATH=;
36 %LET _CLIENTPROJECTPATHHOST=;
37 %LET _CLIENTPROJECTNAME=;
38 %LET _SASPROGRAMFILE=;
39 %LET _SASPROGRAMFILEHOST=;
40
41 ;*';*";*/;quit;run;
42 ODS _ALL_ CLOSE;
43
44
45 QUIT; RUN;

I'm on SAS 9.4M3 using Grid and I can successfully run a standard PROC CONTENTS on a single table in the library, so its not a connection issue. 

proc contents data=dawn.Tbl_Change;
run;

25 GOPTIONS ACCESSIBLE;
26 proc contents data=dawn.Tbl_Change;
27 run;

NOTE: PROCEDURE CONTENTS used (Total process time):
real time 0.14 seconds
cpu time 0.04 seconds

28
29 GOPTIONS NOACCESSIBLE;
30 %LET _CLIENTTASKLABEL=;
31 %LET _CLIENTPROCESSFLOWNAME=;
32 %LET _CLIENTPROJECTPATH=;
33 %LET _CLIENTPROJECTPATHHOST=;
34 %LET _CLIENTPROJECTNAME=;
35 %LET _SASPROGRAMFILE=;
36 %LET _SASPROGRAMFILEHOST=;
37
38 ;*';*";*/;quit;run;
39 ODS _ALL_ CLOSE;
40
41
42 QUIT; RUN;

Data Set Name	DAWN.Tbl_Change	Observations	.
Member Type	DATA	Variables	10
Engine	SQLSVR	Indexes	0
Created	.	Observation Length	0
Last Modified	.	Deleted Observations	0
Protection		Compressed	NO
Data Set Type		Sorted	NO
Label
Data Representation	Default
Encoding	Default

Alphabetic List of Variables and Attributes
#	Variable	Type	Len	Format	Informat	Label
5	ChangeDateTime	Num	8	DATETIME22.3	DATETIME22.3	ChangeDateTime
1	ChangeID	Num	8	11	11	ChangeID
4	FieldName	Char	50	$50.00	$50.00	FieldName
9	ForeignKeyFromValue	Num	8	11	11	ForeignKeyFromValue
10	ForeignKeyToValue	Num	8	11	11	ForeignKeyToValue
6	FromValue	Char	255	$255.00	$255.00	FromValue
3	RecordIDFK	Num	8	11	11	RecordIDFK
2	TableName	Char	50	$50.00	$50.00	TableName
7	ToValue	Char	255	$255.00	$255.00	ToValue
8	UserName	Char	50	$50.00	$50.00	UserName

Any suggestions or ideas on a solution?

Thanks

Andrea

[Reeza · Accepted solution]

Data on a server isn't stored in the dictionary tables, sadly 😞

Most servers have their own versions so if you can do a pass through query you may be able to access the information directly from the server. 

[AndreaMacLeod]

Hi Reeza - thank you for your quick response. Once I thought about it - your answer made perfect sense.

Sorry for the delay in replying and thanking you, but I decided to pursue the pass through query first so I could post a solution as well.

Here's what my Data Analyst, Doug Char came up with:

PROC SQL;
CONNECT TO SQLSVR (DATAsrc=SQL404 USER="&dawn_user" PW="&dawn_pw");
CREATE TABLE tbl_names AS
SELECT * FROM CONNECTION TO SQLSVR
(SELECT table_name FROM information_schema.tables WHERE table_type='BASE TABLE');
DISCONNECT FROM SQLSVR;

PROC SQL;
CONNECT TO SQLSVR (DATAsrc=SQL404 USER="&dawn_user" PW="&dawn_pw");
CREATE TABLE all_data AS
SELECT * FROM CONNECTION TO SQLSVR
(SELECT OBJECT_SCHEMA_NAME(t.[object_id], db_id()) AS [Schema],
t.[name] AS [table_name], ac.[name] AS [column_name],
ty.[name] AS system_data_type, ac.[max_length],
ac.[precision], ac.[scale], ac.[is_nullable], ac.[is_ansi_padded]
FROM sys.[tables] AS t
INNER JOIN sys.[all_columns] ac
ON t.[object_id] = ac.[object_id]
INNER JOIN sys.[types] ty
ON ac.[system_type_id] = ty.[system_type_id] AND ac.[user_type_id] = ty.[user_type_id]
WHERE t.[is_ms_shipped] = 0
ORDER BY t.[name], ac.[column_id]);
DISCONNECT FROM SQLSVR;