SQL Server 2014 - How to list a view

Mark
Mark used Ask the Experts™
on
How can I list a view in SQL Server 2014? In SQL Sever 2015 it was sp_helptext @objname = 'myview'. That doesn't work in 2014. I get the error "There is no text for object 'myview'"
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Define 'list a view'.  Perhaps..
SELECT * FROM sys.views

Open in new window

Author

Commented:
No, I'm looking for a list of views. I'm looking for how to list the "create view ..." etc. of a given view.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I get the error "There is no text for object 'myview'"
Did you run the command in the correct database? Also, verify if you added the schema name.
use databasename
go
sp_helptext @objname = 'schema.myview'

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Well, here's the interesting thing. When I run the sp_helptext in the Server Management Studio, it works. When I run it from a remote host inside an java program I get:

Select Failed: 15197 There is no text for object 'myview'

This is true whether I use a Microsoft jdbc driver or a 3rd party driver.

However, if I run the same procedure from the same java program connecting to the SQL Server 2005 database, it works.

Thoughts?
IT Engineer
Distinguished Expert 2017
Commented:
If you sure that you're running in the same database context then the issue may be with the user permissions. Is the user that are running from java program the same as who's running in SSMS?
At least VIEW DEFINITION privilege is needed to obtain the object text.

Author

Commented:
Ah ha! That's the problem. I ran using 'sa' in SSMS, but a normal user in the java program. When I ran as 'sa' with the java program, it worked.

Now I just have to give VIEW DEFINITION priv to the normal user.

Author

Commented:
Yup, I did "grant view definition to public" and that solved the problem. Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial