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
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
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
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.