Check view definition

Learning objective: The objective of this tutorial is to teach you the different ways to check view definition in SQL Server Database.

While working in SQL Server database sometimes it may need to require to check the view definition of already created objects like views, tables, stored procedure, user-defined functions, etc. There are many ways to check the definition of an existing view in SQL Server Database.

Checking view definition using the sp_helptext stored procedure

One of the easiest ways to check the view definition is by using sp_helptext stored procedure. The sp_helptext stored procedure returns the definition of the view.

To get the view’s definition, you need to pass the name of the view to the sp_helptext stored procedure. For example, the following statement returns the definition of sales.product_catalog view:

EXEC sp_helptext 'dbo.Vwemp';

The following is the output of the above query.

Getting the view definition using OBJECT_DEFINITION() function

Another way to get the view definition is using OBJECT_DEFINITION() function. Here you have to use OBJECT_ID() function along with OBJECT_DEFINTION() function as follows:

SELECT OBJECT_DEFINITION (
     OBJECT_ID (
         'dbo.Vwemp'
     )
 ) view_info;

The following output shows the result of the above statement.

Checking view definition using the sys.sql_modules catalog

Another alternate method to get the view definition using the system catalog sys.sql_modules and the OBJECT_ID function.

SELECT
     definition,
     uses_ansi_nulls,
     uses_quoted_identifier,
     is_schema_bound
 FROM
     sys.sql_modules
 WHERE
     object_id
     = object_id(
             'dbo.Vwemp'
         );

In the above query, you pass the view name to the OBJECT_ID() function which returns the identification number of the view to the sys.sql_modules catalog.

Here is the output of the above query:

Note that you need to export the result to the text format in order see the SELECT statement as above.

To show the result as text, from the query editor, you can press CTRL+T or click the Result to Text button as shown in the following screenshot.

Summary: In this tutorial, you have learned various ways to check the view definition in SQL Server definition.