How to Get Source Code of an SQL View Using Proc SQL

[melc]

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;

[SASKiwi]

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 🙂

[melc]

This worked perfectly. Thank you so much!

[Patrick · Accepted solution]

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.