Retrieving SQL Server View Definitions: A Comprehensive Guide

Views are virtual tables that do not store data themselves but are instead derived from one or more underlying base tables or other views. They provide an abstraction layer that hides the complexity of the underlying data structures, allowing users to access data more conveniently.

In SQL Server, views can be created using the CREATE VIEW statement. However, once a view is created, its definition is not readily visible to users querying the view. To understand what columns a view exposes or how it derives its data, developers often need to retrieve the view definition.

This guide covers several methods to retrieve the definition of a view in SQL Server, both through SSMS (SQL Server Management Studio) and T-SQL commands.

Using SSMS to View View Definitions

SSMS provides a simple graphical interface to view the definition of a SQL Server view.

Using Object Explorer

In the Object Explorer pane, navigate to the view under Databases > [YourDatabase] > Views. Right click on the view and select Properties.

This opens up a properties window that shows the view definition under the Definition tab:

Along with the full SQL view definition text, additional metadata is shown indicating whether ANSI nulls, quoted identifiers, encryption etc. were used in defining the view.

Using Design View

An alternate way is to right-click the view in Object Explorer and select Design.

This opens up a graphical design window showing all columns selected in the view, their data types and any additional properties.

The SQL view definition text can be accessed by clicking the ellipsis (…) next to the view name and selecting Script View as > CREATE To > New Query Editor Window.

This extracts and displays the full view definition SQL in a new SSMS query window:

So SSMS provides a couple options through its graphical interface to easily view the definition of a view. Next let‘s look at some T-SQL methods.

Retrieving View Definitions using T-SQL

T-SQL provides several system stored procedures and functions to retrieve the definition of a view.

Using sys.sql_modules

The sys.sql_modules catalog view contains the definitions of all database objects, including views.

To get a view definition using this catalog view:

SELECT definition 
FROM sys.sql_modules
WHERE object_id = OBJECT_ID(‘viewname‘);

This returns only the view definition text. To also return metadata on whether ANSI nulls, quoted identifiers etc. were used:

SELECT definition, uses_ansi_nulls, uses_quoted_identifier, is_schema_bound
FROM sys.sql_modules
WHERE object_id = OBJECT_ID(‘viewname‘);

Using sp_helptext

The sp_helptext stored procedure can also be used to retrieve just the definition text of a view:

EXEC sp_helptext ‘viewname‘;

Using OBJECT_DEFINITION()

Finally, the OBJECT_DEFINITION() function returns the SQL text defined against any database object.

For a view, we can use:

SELECT OBJECT_DEFINITION(OBJECT_ID(‘viewname‘)) AS view_definition; 

The output contains only the view definition SQL.

So in summary, SSMS and T-SQL provide several straightforward ways to view the SQL definition of views in SQL Server databases.

Now let‘s go a bit deeper and learn how view definitions are actually stored in SQL Server, how this storage works under the hood when views reference other views, and a technique to extract the final base table references.

How View Definitions are Stored Internally

When a view is created using CREATE VIEW, SQL Server does not store the verbatim text of the entire view definition as one would expect.

Instead, certain key properties are persisted in system catalog tables while the base select query is stored in sys.sql_modules fragmented across multiple rows.

Definition Storage in Catalog Views

Key properties like the view name, schema, column list etc. are stored in catalog views like sys.views, sys.columns and sys.objects.

For example, in sys.views:

In sys.columns, one row per column:

And in sys.objects, the parent object ID and type (‘V‘ for view):

Definition Storage in sys.sql_modules

The base select query that defines the view is stored in rows within the sys.sql_modules catalog view.

When we query sys.sql_modules, the full output is reconstructed by concatenating the definition fragments from all rows pertaining to that view definition, ordered by usesql.

For example:

So SQL Server does not really store the entire text of lengthy view definitions. It breaks them down into fragments.

This storage mechanism comes into play when a view references other views, as we‘ll see next.

Views Referencing Other Views

When a view definition references other views, SQL Server attempts to "inline" the referenced view SQL code into the parent view definition for efficiency.

For example, consider two views:

CREATE VIEW View1 
AS
SELECT C1, C2 FROM T1;

CREATE VIEW View2
AS  
SELECT C3 FROM View1;

View2 references View1.

When querying sys.sql_modules for View2 definition, we can see the SELECT query from View1 inlined:

However, if the immediate child view also references another parent view, those references are NOT inlined.

For example:

CREATE VIEW View1 
AS
SELECT C1, C2 FROM T1 

CREATE VIEW View2
AS
SELECT C3 FROM View1

CREATE View3
AS
SELECT C4 from View2

View3 references View2 which itself references View1.

The definition retrieved for View3 would show View2 SQL inlined, but the reference to View1 is not expanded:

So SQL Server only inlines the 1-level child view reference into the parent view‘s definition. Grandchild (or further descendant) view references are NOT expanded.

Finding Base Table References from Nested View Definitions

When multiple levels of nested views reference each other, we may want to recursive traverse the definitions to find the base tables ultimately being referenced at the bottom.

Here is one technique using a recursive CTE:

WITH view_tree (object_id, definition, level) AS
(
  -- Anchor member defines view to start from
  SELECT v.object_id, CAST(m.definition AS VARCHAR(MAX)), 1
  FROM sys.views v  
  INNER JOIN sys.sql_modules m
  ON v.object_id = m.object_id
  WHERE v.name = ‘View3‘

  UNION ALL

  -- Recursive member traverses all nested references
  SELECT ref.referenced_major_id, CAST(m.definition AS VARCHAR(MAX)), level + 1
  FROM sys.sql_expression_dependencies sed
  INNER JOIN sys.views ref 
  ON sed.referencing_id = ref.object_id
  INNER JOIN sys.sql_modules m 
  ON ref.object_id = m.object_id 
)
SELECT definition, level 
FROM view_tree;

This recursively parses all child view references to build a tree, finally outputting only the base table references:

So this demonstrates one approach to traverse potentially complex nested chains of view interdependencies to extract the underlying base objects.

Conclusion

This guide covered several methods available in SSMS and T-SQL to retrieve the SQL definition of a view in SQL Server, along with some internals on how view definitions are stored and handled under the hood when nested view references are involved.

Whether we need to inspect view columns, data lineage, uncover performance issues or simply understand functionality during maintenance, easy access to view definitions is extremely useful for DBAs and developers. Hopefully this gives some good techniques to add to your SQL toolbox.