How to programmatically read the SQL statement of a database view

8 Jul 2005 2:43 PM

rstoeck

Does anyone know how to programmatically read the SQL statement of a database
view? I'm using sp_helptext to read stored procedures and user defined
functions, but haven't come upon anything to read the views. (BTW, I'm using
Java). Thanks!

8 Jul 2005 2:49 PM

Daniel Wilson

sp_helptext works w/ views as well

hth,

--
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown Company

Show quote

"rstoeck" <rsto***@discussions.microsoft.com> wrote in message
news:5B6B513F-06E8-4E29-8803-EC628DCE0156@microsoft.com...
> Does anyone know how to programmatically read the SQL statement of a
database
> view? I'm using sp_helptext to read stored procedures and user defined
> functions, but haven't come upon anything to read the views. (BTW, I'm
using
> Java). Thanks!

8 Jul 2005 2:51 PM

Alejandro Mesa

Use the same sp. Are you having a problem?

Example:

use northwind
go

exec sp_helptext 'dbo.Category Sales for 1997'
go

AMB

Show quote

"rstoeck" wrote:

> Does anyone know how to programmatically read the SQL statement of a database
> view? I'm using sp_helptext to read stored procedures and user defined
> functions, but haven't come upon anything to read the views. (BTW, I'm using
> Java). Thanks!

8 Jul 2005 3:02 PM

rstoeck

Duh, I should have tried that. I'm using java.sql.DatabaseMetaData
getProcedures() to retrieve the sp and udf modules, and it did not work for
views. I'm barking up the wrong tree here. Thanks for your quick response!
-Rob

Show quote

"Alejandro Mesa" wrote:

> Use the same sp. Are you having a problem?
>
> Example:
>
> use northwind
> go
>
> exec sp_helptext 'dbo.Category Sales for 1997'
> go
>
>
> AMB
>
> "rstoeck" wrote:
>
> > Does anyone know how to programmatically read the SQL statement of a database
> > view? I'm using sp_helptext to read stored procedures and user defined
> > functions, but haven't come upon anything to read the views. (BTW, I'm using
> > Java). Thanks!

8 Jul 2005 2:51 PM

Armando Prato

You should also be able to read the view body via sp_helptext

Another option is to use INFORMATION_SCHEMA.VIEWS
(see BOL for details and limitations)

Show quote

"rstoeck" <rsto***@discussions.microsoft.com> wrote in message
news:5B6B513F-06E8-4E29-8803-EC628DCE0156@microsoft.com...
> Does anyone know how to programmatically read the SQL statement of a
database
> view? I'm using sp_helptext to read stored procedures and user defined
> functions, but haven't come upon anything to read the views. (BTW, I'm
using
> Java). Thanks!