Get Information About a View

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

You can gain information about a view's definition or properties in SQL Server by using SQL Server Management Studio or Transact-SQL. You may need to see the definition of the view to understand how its data is derived from the source tables or to see the data defined by the view.

In This Topic

• Before you begin:

  Security

• To get information about a view, using:

  SQL Server Management Studio

  Transact-SQL

Before You Begin

Security

Permissions

Using sp_helptext to return the definition of a view requires membership in the public role. Using sys.sql_expression_dependencies to find all the dependencies on a view requires VIEW DEFINITION permission on the database and SELECT permission on sys.sql_expression_dependencies for the database. System object definitions, like the ones returned in SELECT OBJECT_DEFINITION, are publicly visible.

Using SQL Server Management Studio

Get view properties by using Object Explorer

1. In Object Explorer, select the plus sign next to the database that contains the view to which you want to view the properties, and then click the plus sign to expand the Views folder.

2. Right-click the view of which you want to view the properties and select Properties.

   The following properties show in the View Properties dialog box.

   Database
   The name of the database containing this view.

   Server
   The name of the current server instance.

   User
   The name of the user of this connection.

   Created date
   Displays the date the view was created.

   Name
   The name of the current view.

   Schema
   Displays the schema that owns the view.

   System object
   Indicates whether the view is a system object. Values are True and False.

   ANSI NULLs
   Indicates if the object was created with the ANSI NULLs option.

   Encrypted
   Indicates whether the view is encrypted. Values are True and False.

   Quoted identifier
   Indicates if the object was created with the quoted identifier option.

   Schema bound
   Indicates whether the view is schema-bound. Values are True and False. For information about schema-bound views, see the SCHEMABINDING portion of CREATE VIEW (Transact-SQL).

Getting view properties by using the View Designer tool

1. In Object Explorer, expand the database that contains the view to which you want to view the properties, and then expand the Views folder.

2. Right-click the view of which you want to view the properties and select Design.

3. Right-click in the blank space of the Diagram pane and select Properties.

   The following properties show in the Properties pane.

   (Name)
   The name of the current view.

   Database Name
   The name of the database containing this view.

   Description
   A brief description of the current view.

   Schema
   Displays the schema that owns the view.

   Server Name
   The name of the current server instance.

   Bind to Schema
   Prevents users from modifying the underlying objects that contribute to this view in any way that would invalidate the view definition.

   Deterministic
   Shows whether the data type of the selected column can be determined with certainty

   Distinct Values
   Specifies that the query will filter out duplicates in the view. This option is useful when you are using only some of the columns from a table and those columns might contain duplicate values, or when the process of joining two or more tables produces duplicate rows in the result set. Choosing this option is equivalent to inserting the keyword DISTINCT into the statement in the SQL pane.

   GROUP BY Extension
   Specifies that additional options for views based on aggregate queries are available.

   Output All Columns
   Shows whether all columns are returned by the selected view. This is set at the time the view is created.

   SQL Comment
   Shows a description of the SQL statements. To see the entire description, or to edit it, select the description and then select the ellipses (...) to the right of the property. Your comments might include information such as who uses the view and when they use it.

   Top Specification
   Expands to show properties for the Top, Expression, Percent, and With Ties properties.

   (Top)
   Specifies that the view will include a TOP clause, which returns only the first n rows or first n percentage of rows in the result set. The default is that the view returns the first 10 rows in the result set. Use this to change the number of rows to return or to specify a different percentage

   Expression
   Shows what percent (if Percent is set to Yes) or records (if Percent is set to No) that the view will return.

   Percent
   Specifies that the query will include a TOP clause, returning only the first n percentage of rows in the result set

   With Ties
   Specifies that the view will include a WITH TIES clause. WITH TIES is useful if a view includes an ORDER BY clause and a TOP clause based on percentage. If this option is set, and if the percentage cutoff falls in the middle of a set of rows with identical values in the ORDER BY clause, the view is extended to include all such rows.

   Update Specification
   Expands to show properties for the Update Using View Rules and Check Option properties.

   (Update Using View Rules)
   Indicates that all updates and insertions to the view will be translated by Microsoft Data Access Components (MDAC) into SQL statements that refer to the view, rather than into SQL statements that refer directly to the view's base tables.

   In some cases, MDAC manifests view update and view insert operations as updates and inserts against the view's underlying base tables. By selecting Update Using View Rules, you can ensure that MDAC generates update and insert operations against the view itself.

   Check Option
   Indicates that when you open this view and modify the Results pane, the data source checks whether the added or modified data satisfies the WHERE clause of the view definition. If your modification does not satisfy the WHERE clause, you will see an error with more information.

To get dependencies on the view

1. In Object Explorer, expand the database that contains the view to which you want to view the properties, and then expand the Views folder.

2. Right-click the view of which you want to view the properties and select View Dependencies.

3. Select Objects that depend on [view name] to display the objects that refer to the view.

4. Select Objects on which [view name] depends to display the objects that are referenced by the view.

Using Transact-SQL

To get the definition and properties of a view

1. In Object Explorer, connect to an instance of Database Engine.

2. On the Standard bar, select New Query.

3. Copy and paste one of the following examples into the query window and select Execute.

   USE AdventureWorks2022;  
   GO  
   SELECT definition, uses_ansi_nulls, uses_quoted_identifier, is_schema_bound  
   FROM sys.sql_modules  
   WHERE object_id = OBJECT_ID('HumanResources.vEmployee');   
   GO  
   

   USE AdventureWorks2022;   
   GO  
   SELECT OBJECT_DEFINITION (OBJECT_ID('HumanResources.vEmployee')) AS ObjectDefinition;   
   GO  
   

   EXEC sp_helptext 'HumanResources.vEmployee';  
   

For more information, see sys.sql_modules (Transact-SQL), OBJECT_DEFINITION (Transact-SQL) and sp_helptext (Transact-SQL).

To get the dependencies of a view

1. In Object Explorer, connect to an instance of Database Engine.

2. On the Standard bar, select New Query.

3. Copy and paste the following example into the query window and select Execute.

   USE AdventureWorks2022;  
   GO  
   SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name,   
       o.type_desc AS referencing_desciption,   
       COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id,   
       referencing_class_desc, referenced_class_desc,  
       referenced_server_name, referenced_database_name, referenced_schema_name,  
       referenced_entity_name,   
       COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name,  
       is_caller_dependent, is_ambiguous  
   FROM sys.sql_expression_dependencies AS sed  
   INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id  
   WHERE referencing_id = OBJECT_ID(N'Production.vProductAndDescription');  
   GO  
   

For more information, see sys.sql_expression_dependencies (Transact-SQL) and sys.objects (Transact-SQL).