Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 577
  • 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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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