BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
melc
Calcite | Level 5

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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.

View solution in original post

3 REPLIES 3
SASKiwi
PROC Star

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
Calcite | Level 5
This worked perfectly. Thank you so much!
Patrick
Opal | Level 21

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.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 883 views
  • 4 likes
  • 3 in conversation