SQL Server 2014 - How to list a view

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'"
LVL 1
MarkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornSQL Server Data DudeCommented:
Define 'list a view'.  Perhaps..
SELECT * FROM sys.views

Open in new window

MarkAuthor 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ãoMSSQL Senior EngineerCommented:
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

Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

MarkAuthor 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?
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MarkAuthor 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.
MarkAuthor Commented:
Yup, I did "grant view definition to public" and that solved the problem. Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.