How can I Select a View's Creation SQLfrom Information

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » How can I Select a View's Creation SQLfrom...

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

Rate Topic

Display Mode

Topic Options

Author

Message

John.Geranios

Posted Friday, April 18, 2008 3:08 AM

Forum 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

Akeel.Mughal

Posted Friday, April 18, 2008 4:11 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 4:35 AM
Points: 105, Visits: 616

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

John.Geranios

Posted Friday, April 18, 2008 4:20 AM

Forum 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

John Mitchell-245523

Posted Friday, April 18, 2008 4:26 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 4,434, Visits: 7,218

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

GSquared

Posted Friday, April 18, 2008 7:53 AM

SSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571

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 »

Permissions