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.

• How to see view definition in SQL Server
• How to see view definition in SQL server using query
• How to see view definition in SQL Server Management Studio
• How to get view definition in SQL Server
• How to check view definition permission in SQL Server
• How to change view definition in SQL Server
• How to update view definition in SQL Server
• How to set view definition in SQL Server
• How to get all view definitions in SQL Server

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

Many times database administrators need to understand or examine the data coming from a view. And for this take, DBA’s need to know about the underlying tables. However, to fetch these details, we need to see the definition of a view in SQL Server.

SQL Server also facilitates the database administrators to view the definition of a view. And there are many methods to fetch the definition of any view in SQL Server.

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

How to see view definition in SQL Server Management Studio

To fetch the definition of a view in SQL Server, we need to follow the following steps in SQL Server Management Studio.

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

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

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

• After this, we need to select the option for 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 view in the query editor.

Also, check: Comparison Operators in SQL Server

How to see view definition in SQL server using query

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

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 are using 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 get the definition of USA_CustomerView. And after execution, 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 also a built-in function available 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 view definition 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  

In the above example, we are using the OBJECT_DEFINITION() function with a SELECT statement. Moreover, 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.

Method-3: sys.sql_modules

IIn SQL Server, sys.sql_modules is a system catalog view that provides all module definitions in the existing database. And 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 view definition 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. And 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 check view definition permission in SQL Server

The definition of an object plays a critical role as DBA’s or developers might need to test their code. However, users with the public role do not have the authority to view an object’s definition by default. And same is the case with the definition of a view in SQL Server. But, how can we check if a user has permission to see the definition of a view?

The answer to this question is easy. In SQL Server, we can use the sys.fn_my_permissions function to get the list of permission granted on an object.

Let’s see how to use this function to get the list of permission on a view in SQL Server. And check if a user has the VIEW DEFINITION permission or not.

EXECUTE AS USER = 'user_name'
USE database
GO
SELECT * FROM fn_my_permissions('view_name', 'OBJECT')
GO

In the above syntax, we need to provide the following values to check the permissions.

• First, provide the name of a user in place of user_name.
• Second, provide the name of the database in place of database.
• Third, provide the name of the view with schema in place of view_name.

Now, let’s use this syntax to get the list of permissions granted to the user for the given view. And we will use the following query.

EXECUTE AS USER = 'my_user'
USE [sqlserverguides]
GO
SELECT * FROM fn_my_permissions('dbo.USA_CustomerView', 'OBJECT')
GO

However, the above query will return the complete list of permissions. And if we want to check on VIEW DEFINITION permission then, we can use the WHERE clause. Here is a sample output for this query.

Read: SQL Server bulk insert from CSV file

How to change view definition in SQL Server

In this section, we will learn how to change or modify the definition of a view in SQL Server.

So, changing the definition of the view in SQL Server is easy, we just need to use the ALTER VIEW statement. And then, we can specify the new definition for a view. Let’s understand the syntax of using the ALTER VIEW statement first.

USE database

ALTER VIEW view_name
AS

  modified_definition
GO

For better understanding, let’s implement an example using this syntax in SQL Server. And the example is illustrated below.

USE [sqlserverguides]
GO

/* Creating a view */
CREATE VIEW [dbo].[USA_CustomerView]
( [Customer Name], [Customer City], [Customer Country] )
AS
SELECT customer_name, city, country
FROM Customers
GO

/* Modifying the view */
ALTER VIEW [dbo].[USA_CustomerView]
( [Customer Name], [Customer City], [Customer Country] )
AS
SELECT customer_name, city, country
FROM Customers
WHERE country = 'United States';
GO

In the above example, first, we are creating a view in SQL Server. And after this, we are using the ALTER VIEW statement to change the definition of the view.

Read: SQL Server function return table

How to set view definition in SQL Server

In SQL Server, to set the definition of a view, we use the CREATE VIEW query. However, if the view definition already exists, and we have to modify it, we can use the ALTER VIEW query.

Now, we already created a detailed tutorial on How to create a view in SQL Server. In this, we have covered multiple ways to create and set the definition of a view in SQL Server. For more details, refer to this View in SQL Server.

How to get all view definitions in SQL Server

Till now, we have illustrated how to get the definition of a single view in SQL Server. Next, let’s, understand how to get the definition of 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. And 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.

• Arithmetic operators in SQL Server
• Indexed views in SQL Server
• 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. Additionally, we have also illustrated the following set of topics.

• How to see view definition in SQL Server
• How to see view definition in SQL server using query
• How to see view definition in SQL Server Management Studio
• How to get view definition in SQL Server
• How to check view definition permission in SQL Server
• How to change view definition in SQL Server
• How to update view definition in SQL Server
• How to set view definition in SQL Server
• How to get all view definitions in SQL Server