topic Re: How to Get Source Code of an SQL View Using Proc SQL in SAS Programming

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

melc — Wed, 27 Mar 2024 03:42:27 GMT

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;

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

SASKiwi — Wed, 27 Mar 2024 03:54:43 GMT

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 🙂

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

Patrick — Wed, 27 Mar 2024 06:14:04 GMT

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.

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

melc — Thu, 28 Mar 2024 12:32:09 GMT

This worked perfectly. Thank you so much!