4 Ways to Get a View’s Definition using Transact-SQL

Posted on by Ian

This article presents 4 ways of using T-SQL to get the definition of a view in SQL Server.

The view definition is the actual T-SQL statement used to create the view.

Example 1 – The sys.sql_modules System Catalog View

The sys.sql_modules system catalog view returns a row for each object that is an SQL language-defined module in SQL Server.

In other words, you can use this view to return information about objects of various types, including functions, stored procedures, and of course, views.

One of the columns returned with this view is called definition. As the name implies, this returns the definition of the object.

SELECT definition
FROM sys.sql_modules
WHERE object_id = object_id('Website.Customers');

Result:

+--------------+
| definition   |
|--------------|
| 
CREATE VIEW Website.Customers
AS
SELECT s.CustomerID,
       s.CustomerName,
       sc.CustomerCategoryName,
       pp.FullName AS PrimaryContact,
       ap.FullName AS AlternateContact,
       s.PhoneNumber,
       s.FaxNumber,
       bg.BuyingGroupName,
       s.WebsiteURL,
       dm.DeliveryMethodName AS DeliveryMethod,
       c.CityName AS CityName,
       s.DeliveryLocation AS DeliveryLocation,
       s.DeliveryRun,
       s.RunPosition
FROM Sales.Customers AS s
LEFT OUTER JOIN Sales.CustomerCategories AS sc
ON s.CustomerCategoryID = sc.CustomerCategoryID
LEFT OUTER JOIN [Application].People AS pp
ON s.PrimaryContactPersonID = pp.PersonID
LEFT OUTER JOIN [Application].People AS ap
ON s.AlternateContactPersonID = ap.PersonID
LEFT OUTER JOIN Sales.BuyingGroups AS bg
ON s.BuyingGroupID = bg.BuyingGroupID
LEFT OUTER JOIN [Application].DeliveryMethods AS dm
ON s.DeliveryMethodID = dm.DeliveryMethodID
LEFT OUTER JOIN [Application].Cities AS c
ON s.DeliveryCityID = c.CityID
              |
+--------------+
(1 row affected)

I used a command line interface (CLI) with this example, so the result is nicely formatted.

If you use a GUI (such as SSMS or Azure Data Studio) to return the results in a grid, the definition will likely be returned in one long line in a single cell. In such cases you’ll need to do some extra work if you want it displayed in a more readable format. Alternatively, you could use the sp_helptext method below.

Example 2 – The sp_helptext System Stored Procedure

Another method of returning a view’s definition is to use the sp_helptext system stored procedure. As well as returning the definition for a view, it can also return the definition of a user-defined rule, default, unencrypted T-SQL stored procedure, user-defined Transact-SQL function, trigger, computed column, CHECK constraint, or system object such as a system stored procedure.

As mentioned in the previous example, this stored procedure displays the view’s definition across multiple rows. Each row contains 255 characters of the T-SQL definition.

Example:

EXEC sp_helptext 'Website.Customers';

Here’s the result I get when using a GUI (Azure Data Studio):

Screenshot of sp_helptext results when output to a GUI grid.

And here’s what I get using my command line interface:

+--------+
| Text   |
|--------|
| 
        |
| CREATE VIEW Website.Customers
        |
| AS
        |
| SELECT s.CustomerID,
        |
|        s.CustomerName,
        |
|        sc.CustomerCategoryName,
        |
|        pp.FullName AS PrimaryContact,
        |
|        ap.FullName AS AlternateContact,
        |
|        s.PhoneNumber,
        |
|        s.FaxNumber,
        |
|        bg.BuyingGroupName,
        |
|        s.WebsiteURL,
        |
|        dm.DeliveryMethodName AS DeliveryMethod,
        |
|        c.CityName AS CityName,
        |
|        s.DeliveryLocation AS DeliveryLocation,
        |
|        s.DeliveryRun,
        |
|        s.RunPosition
        |
| FROM Sales.Customers AS s
        |
| LEFT OUTER JOIN Sales.CustomerCategories AS sc
        |
| ON s.CustomerCategoryID = sc.CustomerCategoryID
        |
| LEFT OUTER JOIN [Application].People AS pp
        |
| ON s.PrimaryContactPersonID = pp.PersonID
        |
| LEFT OUTER JOIN [Application].People AS ap
        |
| ON s.AlternateContactPersonID = ap.PersonID
        |
| LEFT OUTER JOIN Sales.BuyingGroups AS bg
        |
| ON s.BuyingGroupID = bg.BuyingGroupID
        |
| LEFT OUTER JOIN [Application].DeliveryMethods AS dm
        |
| ON s.DeliveryMethodID = dm.DeliveryMethodID
        |
| LEFT OUTER JOIN [Application].Cities AS c
        |
| ON s.DeliveryCityID = c.CityID
        |
+--------+

This time the results look better when using the GUI.

Example 3 – The OBJECT_DEFINITION() Function

Another way to return a view’s definition is to use the OBJECT_DEFINITION() function. As with the previous methods, this method can also return the definition of other object types.

Here’s an example of using this function:

SELECT OBJECT_DEFINITION(
        OBJECT_ID('Website.Customers')
    ) AS [Definition];

Result:

+--------------+
| Definition   |
|--------------|
| 
CREATE VIEW Website.Customers
AS
SELECT s.CustomerID,
       s.CustomerName,
       sc.CustomerCategoryName,
       pp.FullName AS PrimaryContact,
       ap.FullName AS AlternateContact,
       s.PhoneNumber,
       s.FaxNumber,
       bg.BuyingGroupName,
       s.WebsiteURL,
       dm.DeliveryMethodName AS DeliveryMethod,
       c.CityName AS CityName,
       s.DeliveryLocation AS DeliveryLocation,
       s.DeliveryRun,
       s.RunPosition
FROM Sales.Customers AS s
LEFT OUTER JOIN Sales.CustomerCategories AS sc
ON s.CustomerCategoryID = sc.CustomerCategoryID
LEFT OUTER JOIN [Application].People AS pp
ON s.PrimaryContactPersonID = pp.PersonID
LEFT OUTER JOIN [Application].People AS ap
ON s.AlternateContactPersonID = ap.PersonID
LEFT OUTER JOIN Sales.BuyingGroups AS bg
ON s.BuyingGroupID = bg.BuyingGroupID
LEFT OUTER JOIN [Application].DeliveryMethods AS dm
ON s.DeliveryMethodID = dm.DeliveryMethodID
LEFT OUTER JOIN [Application].Cities AS c
ON s.DeliveryCityID = c.CityID
              |
+--------------+

This time it’s easier to read in my CLI than my GUI grid.

Example 4 – The VIEWS System Information Schema View

The VIEWS system information schema view can also return a view’s definition. One difference between this method and the previous ones is that, the VIEWS information schema view is limited to just views (as the name implies).

It returns a bunch of columns, one of which is the view’s definition. Therefore we can name that column to return just the definition:

SELECT VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'Customers';

Result:

+-------------------+
| VIEW_DEFINITION   |
|-------------------|
| 
CREATE VIEW Website.Customers
AS
SELECT s.CustomerID,
       s.CustomerName,
       sc.CustomerCategoryName,
       pp.FullName AS PrimaryContact,
       ap.FullName AS AlternateContact,
       s.PhoneNumber,
       s.FaxNumber,
       bg.BuyingGroupName,
       s.WebsiteURL,
       dm.DeliveryMethodName AS DeliveryMethod,
       c.CityName AS CityName,
       s.DeliveryLocation AS DeliveryLocation,
       s.DeliveryRun,
       s.RunPosition
FROM Sales.Customers AS s
LEFT OUTER JOIN Sales.CustomerCategories AS sc
ON s.CustomerCategoryID = sc.CustomerCategoryID
LEFT OUTER JOIN [Application].People AS pp
ON s.PrimaryContactPersonID = pp.PersonID
LEFT OUTER JOIN [Application].People AS ap
ON s.AlternateContactPersonID = ap.PersonID
LEFT OUTER JOIN Sales.BuyingGroups AS bg
ON s.BuyingGroupID = bg.BuyingGroupID
LEFT OUTER JOIN [Application].DeliveryMethods AS dm
ON s.DeliveryMethodID = dm.DeliveryMethodID
LEFT OUTER JOIN [Application].Cities AS c
ON s.DeliveryCityID = c.CityID
                   |
+-------------------+

Note that the VIEW_DEFINITION column in this system view has a maximum length of nvarchar(4000). For view definitions larger than this, you can use the OBJECT_DEFINITION() function in the previous example.

The return value of OBJECT_DEFINITION() function is nvarchar(max), so it doesn’t have the character limitation of the VIEW_DEFINITION column (which as mentioned, is nvarchar(4000)).