Pulling SQL Script from Database Using pyodbc

Question

I'm relatively new to databases, so this question may have a simple answer (I've been searching for hours).

I want to write a Python script that pulls the SQL Code stored in SQL Server Management Studio. I can successfully connect to the database using pyodbc and run queries against the database tables, but I would like to be able to pull, for example, the SQL code stored in a procedure, function, view, etc. without running it.

This seems like something that would be relatively simple. I know it can be done in Powershell, but I would prefer to use Python. Is there some sort of module or pyodbc hack that will do this?

FlipperPA · Accepted Answer · 2018-04-09 13:16:08Z

1

You can use the sp_helptext command in SQL Server, which will give you the SQL Server object source code line by line:

stored_proc_text = ""

res = cursor.execute('sp_helptext my_stored_procedure')

for row in res:
    stored_proc_text += row[0]

print(stored_proc_text)

Good luck!

answered Apr 9, 2018 at 13:16

FlipperPA

14.4k5 gold badges46 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jharrison12 Over a year ago

This works great! I have some SET and USE statements at the head of some of the SQL scripts that sp_helptext doesn't seem to pull when running cursor.execute(). Do you happen to know what that area is called and how I can pull those statements using sp_helptext?

FlipperPA Over a year ago

Unfortunately, I don't know of a way. This method pulls the actual procedure itself, after the CREATE PROCEDURE statement. The other statements before that are just run-time statements that aren't permanently stored in the database.

Collectives™ on Stack Overflow

Pulling SQL Script from Database Using pyodbc

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related