Post reply

Best way to extract SQL from a view.

  • January 20, 2010 at 9:00 am

    #218395

    Hi,

    I am developing a web app where the user needs to be able to ammend and save certain views via the web UI. I need to extract the SQL from the view, present to the user, validate and re save. What is the best way to do this ?

    I could use sp_helptext vw... - this returns the view header, and then remove the header, but it seems a bit messy - is it possible to get the sql only.

    Thanks

    Pete Clements.

  • January 20, 2010 at 10:21 am

    #1106177

    Hi Peter,

    SELECT OBJECT_DEFINITION (OBJECT_ID(N'dbo.yourview'))

    will provide you the definition only. I understand you're looking for this. Hope I'm right 🙂

    -- Erik http://blog.rollback.hu

  • January 21, 2010 at 12:54 am

    #1106531

    thanks,

    but this returns the create view ... as well as the sql, same as sp_helptext. I really wanted just to

    return the body sql of the view

  • January 21, 2010 at 10:51 pm

    #1107097

    You should not get "Text" in ur sp_helptext body of the view!! am i rite??? Is this u really looking for??? need more info??

    Regards,
    Saravanan

  • January 21, 2010 at 11:20 pm

    #1107111

    Hi Saravanan,

    The OP says, he got the text when run the sp_helptext, but along with the comment mentioned in the top of the view/procedure, OP need to trim the commend in the top and only shows the exact used content of the view/procedure

    Example

    /*

    THIS VIEW FOR THE TEST OF

    THE QUALITY !!!!!

    */

    Create View MYVIEW

    as

    select 'AAA' as col1

    When you run the exec sp_helptext MYVIEW you get along with

    /*

    THIS VIEW FOR THE TEST OF

    THE QUALITY !!!!!

    */

    But ask question for any sql to show only like

    Create View MYVIEW

    as

    select 'AAA' as col1

  • January 22, 2010 at 12:03 am

    #1107123

    Hi ,

    Compile the attached SP ,pass your view name as the parameter and execute. Hope it will work.

    Revert in case of any issue.

    Regards,
    Saravanan

  • January 22, 2010 at 12:35 am

    #1107130

    Hi Saravanan,

    Nice work,

    But, is its works only in the 2000?

    And just fine turn this code to suit with the below VIEW

    /*

    THIS INFO ALREADY IN THE

    XYZ VIEW, THEN WHY WE NEED TO HAVE AND

    CREATE ANOTHER VIEW, BECAUSE IT’S TRIM!!!!

    */

    CREATE View MYVIEW

    as

    select 'AAA' as col1

    exec sp_helptextview ‘MYVIEW’

  • January 22, 2010 at 1:04 am

    #1107138

    Hi Arun,

    It works in all SQl versions.... SQL injection is not allowed.... let me try and post reply for your scenario.....!!

    Regards,
    Saravanan

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply