April 18, 2008 at 3:08 am
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
🙂
April 18, 2008 at 4:11 am
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.
April 18, 2008 at 4:20 am
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...
😀
April 18, 2008 at 4:26 am
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
April 18, 2008 at 7:53 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply