• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
sqlserver tutorial

SQL Server Tutorial

SQL Server Tutorial for Beginners

  • HOME
  • START HERE
  • BASICS
  • ADVANCED
    • Index
    • Views
    • Triggers
    • Stored Procedures
    • User-defined Functions
  • FUNCTIONS
Home / SQL Server Views / Check view definition

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.

check view definition in sql server

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.

check view definition in sql server

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:

check view definition in sql server

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.

Was this tutorial helpful?
YesNo
« Previous: SQL Server Rename View
SQL Server Indexed View :Next »

Primary Sidebar

DATA MANIPULATION

  • SELECT
  • SELECT TOP
  • SELECT DISTINCT
  • OFFSET FETCH
  • ORDER BY
  • GROUP BY
  • BETWEEN
  • LIKE
  • ALIAS
  • HAVING
  • AND
  • OR
  • IN
  • WHERE
  • SELECT INTO
  • INSERT
  • INSERT Multiple rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE
  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE CONSTRAINT
  • NOT NULL CONSTRAINT
  • SUBQUERY
  • CORRELATED SUBQUERY
  • JOINS
  • CROSS JOIN
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN
  • SELF JOIN
  • UPDATE Join
  • CASE
  • COALESCE
  • NULL
  • NULLIF
  • UNION
  • INTERSECT
  • MERGE
  • EXCEPT
  • EXISTS
  • GROUPING SET
  • PIVOT
  • ROLLUP
  • CUBE

DATA DEFINITION

  • CREATE DATABASE
  • DROP DATABASE
  • CREATE SCHEMA
  • ALTER SCHEMA
  • DROP SCHEMA
  • CREATE TABLE
  • RENAME TABLE
  • DROP TABLE
  • TRUNCATE TABLE
  • IDENTITY column
  • Sequence
  • ALTER TABLE ADD Column
  • ALTER TABLE ALTER Column
  • ALTER TABLE DROP Column

Footer

About

SQLServerTutorial.org provides free tutorials and guide on SQL Server for Developers, Database Administrators, and Solution Architects who want to get started SQL Server quickly.

Recent Posts

  • SQL Server DROP VIEW
  • SQL Server Indexed View
  • Check view definition
  • SQL Server Rename View
  • SQL Server List Views

Quick Links

  • About
  • Contact Us
  • Privacy Policy
  • SQL Server Index
  • SQL Server Views
  • Terms of Use

Copyright © 2023 www.sqlservertutorial.org. All Rights Reserved.