How to see view definition in SQL Server

In this SQL Server tutorial, we will understand how to see view definition in SQL Server. Additionally, we will also illustrate the following set of topics. But, before we start this topic, we recommend you learn about the basics of View in SQL Server.

How to see view definition in SQL Server

In this section, we will illustrate all the valid methods for this task. Moreover, these methods include the use of SQL Server Management Studio and Transact-SQL queries.

Approach-1 Using SQL Server Management Studio

To see view definition in SQL Server, follow the below steps.

1. First, run SQL Server Management Studio and connect to the required database instance.
2. Next, from the Object Explorer, first, expand the Database instance and then expand the Databases directory.
3. After this, right-click the required database and then navigate to the Tasks option, and select the Generate Script option. This will open a new Generate Script window.

4. In the new window, click on the Next button to navigate to the new page.

5. Next, select the “Select specific database objects” option. Then, from the given list, expand the Views directory, tick mark the required view, and click on Next.

6. After this, we must select how a script should be saved. In our case, we have selected the “Open in a new query window” option.

• In the end, click on the Next button, and it will open the script for viewing in the query editor.

Also, check Comparison Operators in SQL Server

Approach-2 Using query

In SQL Server, there are multiple queries that we can use to know the definition of a view. However, this section will illustrate three well-known methods to fetch view definitions.

Method-1: sp_helptext

In SQL Server, sp_helptext is a system-stored procedure that displays the definition of different objects. These objects include views, stored procedures, functions, triggers, etc. Now, let’s understand the syntax of using sp_helptext in SQL Server.

EXEC sp_helptext [ @object_name = ] 'name' [ , [ @column_name = ] computed_column_name ] 

In the above syntax, we use the EXEC statement to execute the sp_helptext procedure. Moreover, this procedure also has the following parameters.

• object_name: This parameter is used to specify the object name of a database. This object name could be the name of a stored procedure, function, view, etc.
• column_name: This parameter is used to specify the computed column name.

Now, let’s use this syntax to get the definition of a view in SQL Server. And we will use the following query.

USE [sqlserverguides]
GO

EXEC sp_helptext 'dbo.USA_CustomerView'; 

In the above example, we are using the sp_helptext procedure to define USA_CustomerView. After execution, the SQL Server will return the definition of this view. The example is shown below.

Moreover, from the definition of the view, we can observe that the USA_CustomerView will return the records from the Customers table where the country is the United States.

Method-2: OBJECT_DEFINITION

OBJECT_DEFINITION() is a built-in function in SQL Server that returns definitions of a specified object. Moreover, we can easily use it by following the given syntax.

OBJECT_DEFINITION ( object_id ) 

Now, let’s understand how to use this function to get a definition of view in SQL Server. An example related to this is shown below.

USE [sqlserverguides];   
GO  
SELECT OBJECT_DEFINITION (OBJECT_ID('USA_CustomerView')) AS [View Definition];   
GO  

The above example uses the OBJECT_DEFINITION() function with a SELECT statement. Moreover, to get the object ID, we use the OBJECT_ID function. And in the OBJECT_ID function, we have specified the required view name. In the end, we will get an output similar to this.

Method-3: sys.sql_modules

In SQL Server, sys.sql_modules is a system catalog view that provides all module definitions in the existing database. We can smoothly get the definition of a view using the object ID. Here is the syntax of using the sys.sql_modules in SQL Server.

USE database
GO

SELECT definition AS [View Definition]
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('view_name')

Now, let’s understand how to use this function to get a definition of view in SQL Server. An example related to this is shown below.

USE [sqlserverguides]
GO

SELECT definition AS [View Definition]
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('USA_CustomerView')

• In the above example, we are using the SELECT statement to fetch the definition column from the sys.sql_modules view. To filter the definition using the object ID, we are using the WHERE clause.
• However, to get the object id, we are using the OBJECT_ID function. And in the OBJECT_ID function, we have specified the required view name.

In the end, we will get an output similar to this.

Read: How to delete a view in SQL Server

How to get all view definitions in SQL Server

Until now, we have illustrated how to define a single view in SQL Server. Next, let’s understand how to define all the views in a database.

Now, to execute this task, we can use two catalog views from SQL Server. The name of the first view is sys.objects, and the second is sys.sql_modules. Let’s understand their use with the help of an example. The query for this example is given below.

USE [sqlserverguides]
GO

SELECT name, definition 
FROM sys.objects o
JOIN sys.sql_modules m on m.object_id = o.object_id
AND o.type='V'

In the above example, we are performing a JOIN between sys.objects and sys.sql_modules. And we are fetching the view name and its definition. Here is the sample output for this query.

You may also like to read the following SQL Server tutorials.

• SQL Server scheduled stored procedure
• Alter Stored Procedure in SQL Server
• How to insert a row in a view in SQL Server
• Rename stored procedure in SQL Server
• Temp table in stored procedure in SQL Server
• Difference between table and view in SQL Server

So, in this tutorial, we have understood how to see view definition in SQL Server.