Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

Get Information About a View

You can gain information about a view’s definition or properties in SQL Server 2012 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.

Important note Important

If you change the name of an object referenced by a view, you must modify the view so that its text reflects the new name. Therefore, before renaming an object, display the dependencies of the object first to determine if any views are affected by the proposed change.

In This Topic

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.

Arrow icon used with Back to Top link [Top]

Get view properties by using Object Explorer

  1. In Object Explorer, click 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 click 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, click the description and then click 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 do 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.

Arrow icon used with Back to Top link [Top]

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, click New Query.

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

    USE AdventureWorks2012;
    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 AdventureWorks2012; 
    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, click New Query.

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

    USE AdventureWorks2012;
    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).

Arrow icon used with Back to Top link [Top]

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.