When using Proc SQL to connect to an SQL Server database, is it possible to find the source code/SQL script that was used to create a View?
I thought an "execute sp_helptext" statement might work. If so, I'm not certain of the proper syntax. I thought the following would work.
proc sql;
connect to server (user=userid password=password path=database_name);
execute sp_helptext vwTable;
disconnect from server;
quit;
Easiest would be to use a database client like MS SQL Server Management Studio or DBeaver. Using SAS you need to get the result back to SAS in order to see it.
Something along the line of below partially tested code should work. Replace DBO with the name of the database under which the view is stored.
libname mydb sqlsvr .... dbmax_text=32767;
proc sql;
connect using mydb;
select * from connection to mydb
(
SELECT OBJECT_DEFINITION (OBJECT_ID(N'[DBO].[VWTABLE]')) AS [VIEW_DEFINITION];
);
disconnect from mydb;
quit;
What I could test: The SQL Server query on a view that was available on my server and for which I had the necessary permissions.
You can execute external database stored procedures via the SAS EXECUTE statement:
proc sql;
connect to server (user=userid password=password path=database_name);
execute(execute sp_helptext vwTable;) by server;
disconnect from server;
quit;
Not sure what will happen to the SP output though 🙂
Easiest would be to use a database client like MS SQL Server Management Studio or DBeaver. Using SAS you need to get the result back to SAS in order to see it.
Something along the line of below partially tested code should work. Replace DBO with the name of the database under which the view is stored.
libname mydb sqlsvr .... dbmax_text=32767;
proc sql;
connect using mydb;
select * from connection to mydb
(
SELECT OBJECT_DEFINITION (OBJECT_ID(N'[DBO].[VWTABLE]')) AS [VIEW_DEFINITION];
);
disconnect from mydb;
quit;
What I could test: The SQL Server query on a view that was available on my server and for which I had the necessary permissions.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.