Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How can I Select a View's Creation SQLfrom Information_Schema? Expand / Collapse
Author
Message
Posted Friday, April 18, 2008 3:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 18, 2008 4:15 AM
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
:)

Post #486990
Posted Friday, April 18, 2008 4:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 6:45 AM
Points: 105, Visits: 633
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.
Post #487026
Posted Friday, April 18, 2008 4:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 18, 2008 4:15 AM
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...

:D
Post #487031
Posted Friday, April 18, 2008 4:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 5,490, Visits: 10,347
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
Post #487037
Posted Friday, April 18, 2008 7:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #487204
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse