Get Information About Views

Use sp_help and sp_helptext to Display View Information

The sp_help command reports on a view. To display the text of the create view statement, use sp_helptext.

Use sp_help to report on a view:

sp_help hiprice
----------------

In the evaluated configuration, the system security officer must reset the allow select on syscomments.text column configuration parameter. (See evaluated configuration in the Glossary for more information.) When this happens, you must be the creator of the view or a system administrator to view the text of a view through sp_helptext.

To display the text of the create view statement, execute sp_helptext:

sp_helptext hiprice 
# Lines of Text 
-----------------
3 (1 row affected) text                                                                                                                                                                                                                                                             --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --SAP ASE has expanded all '*' elements in the following statement create view hiprice as select titles.title_id, titles.title, titles.type, titles.pub_id, titles.price, 
titles.advance, titles.total_sales, titles.notes, titles.pubdate, 
titles.contract from titles where price > $15 and advance > $5000(3 rows affected)
(return status = 0)

If the source text of a view was encrypted using sp_hidetext, SAP ASE displays a message advising you that the text is hidden. See the Reference Manual: Procedures.

Use sp_depends to List Dependent Objects

sp_depends lists all the objects that the view or table references in the current database, and all the objects that reference that view or table.

sp_depends titles

Things inside the current database that reference the object. 
object               type 
-------------        --------------------------- 
dbo.history_proc     stored procedure
dbo.title_proc       stored procedure
dbo.titleid_proc     stored procedure
dbo.deltitle         trigger
dbo.totalsales_trig  trigger
dbo.accounts         view
dbo.bookview         view
dbo.categories       view
dbo.hiprice          view
dbo.multitable_view  view
dbo.titleview        view
 
(return status = 0)

List All Views in a Database

sp_tables lists all views in a database.

The syntax is:

sp_tables @table_type = "’VIEW’"

Find an Object Name and ID

The system functions object_id and object_name identify the ID and name of a view.

For example:

select object_id("titleview")

----------
 480004741

Object names and IDs are stored in the sysobjects table.