Best way to extract SQL from a view.

Question

Post reply

Best way to extract SQL from a view.

Peter Clements-368786

SSC Enthusiast

Points: 159
More actions
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.

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

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

Erik Bitemo SSCrazy Points: 2062 More actions · Answer 1

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

Peter Clements-368786 SSC Enthusiast Points: 159 More actions · Answer 2

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

Saravanan T SSCrazy Points: 2506 More actions · Answer 3

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

arun.sas SSChampion Points: 11831 More actions · Answer 4

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

Saravanan T SSCrazy Points: 2506 More actions · Answer 5

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

arun.sas SSChampion Points: 11831 More actions · Answer 6

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’

Saravanan T SSCrazy Points: 2506 More actions · Answer 7

Hi Arun,

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

Regards,
Saravanan