Returning the contents of an SQL server stored procedure as a string?

Rosebud · February 5, 2019, 9:26am

Hi there,

I'm wondering if its possible to get the contents of an sql procedure using R. Most of the research Ive done only talks about executing from R but I need the actual contents of the procedure so I can modify it and run in the database.

jdlong · February 5, 2019, 2:08pm

Yes. The specifics depend on your exact DB connection. But the gist is to use dbGetQuery

Rosebud · February 5, 2019, 7:32pm

Thanks a lot! Happy to hear that. My DB is Microsoft SQL server. How exactly do I use dbGetquery for this? My understanding is this function runs sql commands to the database while I just want the contents of the procedure (not the result)

jdlong · February 5, 2019, 8:09pm

took me a couple of reads to understand what you were after. This SO question is what you are after

to use with dbGetQuery() you'd connect to your database, then:

dbGetQuery(con, "EXEC sp_helptext 'your_stored_proc';")

The text will end up in a data.frame with one column and a row for each line of the query.

system · February 26, 2019, 8:11pm

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.