SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How can I Select a View's Creation SQLfrom Information_Schema?


How can I Select a View's Creation SQLfrom Information_Schema?

Author
Message
John.Geranios
John.Geranios
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 2
Hello
I am writing an application in VB .net.
I need to perform select queries using the SQL of pre existing Views in the server, after some slight "surgery" on it. ( So I cannot just write SELECT* from View_A and be good with it.)

I need to be able to retrieve the SQL of a view, by just providing its name as a parameter.

I have found the solution:

SELECT TABLE_NAME, VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE (TABLE_NAME = 'Report_View_A')

It is what I want but....

Some views are very complex thus long...(have a large SQL script)
So in this cases i get a "Trimmed" VIEW_DEFINITION as return...

How can i get the full View definition SQL?
Where does SQL Server keeps the full SQL of the views?

Could you please help?....

Thanx in advance..

A.I.G
Smile
Akeel.Mughal
Akeel.Mughal
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 634
In the Object Explorer for the SQL Server, expand the database, expand views, find the view, right click and Script view as > Create To > New Query window.
John.Geranios
John.Geranios
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 2
Thanx a lot for the anwer
I am aware of this,
I need to be able to do this dynamically from the .NET application
not manually from the Enterprise manager.

It is part of an automated procedure, and the View name and other parameters (Like the Database and Server name) are supplied run-time...

BigGrin
John Mitchell-245523
John Mitchell-245523
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18350 Visits: 16178
sp_helptext should give you what you're looking for. But you may have to grant your user higher permissions than it already has - check out the Permissions section of the sp_helptext topic in Books Online.

John
GSquared
GSquared
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30001 Visits: 9730
In SQL 2005, there's a view called "sys.sql_modules", which has the definition (create script) for views, procs, etc. Books Online has the specifics.

Join that to sys.views to get the name (or to sys.all_objects) on object_id column.

If you can select from that, you should get what you need.

Edit: I just tested this with a proc with just over 75,000 characters, and it returned as a single row with the full text in it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search