Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 579
  • Last Modified:

Ability to export the sql behind a SP / view / query to an ascii file ?

Is there a way that I can export the SQL behind a stored procedure or view, into a text file, or preferably into a recordset string field.

My absolute ideal would to have a stored procedure that returns a recordset with the database object name and the SQL in the object, be it a stored procedure or a view, that I can use to get ascii copies of all my db objects, such that I can see their progress over time in CVS.

Any ideas ?

Regards
Marts
0
mj_cole
Asked:
mj_cole
  • 3
2 Solutions
 
arbertCommented:
sure, if you want it in a recordset, just execute the sp_helptext stored proc with the stored procedure you want the text of:

rs=conn.execute("sp_helptext mystoredproc")


You can also right click on your database (in enterprise manager) and generate SQL Scripts for your entire database or certain objects.

Brett
0
 
arbertCommented:
You can also write queries against the information.schema_views (located in the master database) to retrieve information....

Brett
0
 
mj_coleAuthor Commented:
sp_helptext  seems to do the job :)

is there a master SP to return all the stored procedures and views, such that I can then write my own SP to get the list and then call sp_helptext on that list ?

Regards
Marts

0
 
arbertCommented:
Nope, but that's where the information_schema.views come in.....

You can run this from your database and it will list the procs and views for you:

select 'proc', routine_name from information_schema.routines
union
select 'View',view_name from information_schema.view_table_usage

However, using the information schema views, you can also return the text of the views and procs and you don't have to use sp_helptext.

Brett
0
 
Scott PletcherSenior DBACommented:
Actually I think the information_schema views only return the first 4K of object text, so it's not a safe way to get the source.  You really need to use sp_helptext.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now