Grabbing 'View' code 1

[DaveJunior]

I am an ASP programmer and need to grab the SQL code for a view within a database.

Can anyone tell me if this is even possible or better still, how to do it.

cheers.

 

[rt63]

sp_helptext <view_name>

RT

 

[DaveJunior]

That works great for any view that I create on the server as DBO, however, I also have some views that were created through ADODB in ASP. These have an owner of [NT AUTHORITY\ANONYMOUS LOGON] and I cannot reference them in any of the following ways:

sp_helptext myView

sp_helptext [NT AUTHORITY\ANONYMOUS LOGON].myView

sp_helptext CMP.[NT AUTHORITY\ANONYMOUS LOGON].myView

Any ideas?

 

[rt63]

There are two options to do this.

1. Login as the anonymous user and then use the sp_helptext to get the code of the view.

2. The other method is to dig into the system tables as follows

select id from sysobjects where name = <your view name>

Once you have this id

select text from syscomments where id = <id of your view>

RT

 

[DaveJunior]

Excellent - Thank you