SQL Server Get View Definition

November 19, 2022 | by Arround The Web | No comments

SQL Server Get View Definition

In some instances, you may come across an instance where you need to gather information about the properties of a given view. This can help you understand how data in the view is retrieved from the source table and the actual data structure in the view.

This tutorial will show you two primary methods to get view definitions in SQL Server.

SQL Server Show View Definition – SSMS

The first method you can use to get a view’s definition is SQL Server is using the SQL Server Management Studio.

SSMS provides a simple and easy-to-read graphical interface with the property of a given view.

In Object Explorer, expand the database on which the target view resides. Then, navigate to the Views folder and right-click the target view.

Select the Properties option to view the view’s definition.

This should open a new Window with the information about the view.

Included information are:

Database – the name of the database where the view is located.
Server – the name of the current server instance.
User – the name of the currently connected user.
Created date – the date on which the view was created.
Name – the name of the view.
schema – the schema holding the view.
ANSI NULLs – specifies whether the view includes the ANSI NULLs option during creation.
Encrypted – is the view encrypted or not.
Quoted identifier – defines if the view is created with the quoted identifier option.
Schema bound – specifies if the view is schema bound or not.

You can also get the view information using the Design Tool in SSMS. Right-Click the view and select Design.

This should open the designer window showing information about the view.

SQL Server Show View Definition – Transact-SQL

Regarding T-SQL options, there are various ways we can fetch the definition of a given view.

The first is querying the sp.sql_modules catalog. The query is as shown:

select definition, uses_ansi_nulls, uses_quoted_identifier, is_schema_bound from sys.sql_modules where object_id = OBJECT_ID('sql_view');

The query above should return similar information on how to use the SSMS properties option.

You can also use the sp_helptext procedure to get the view definition. An example query is as shown:

exec sp_helptext 'dbo.sql_view';
Output:
Text
----------------------------------------------------------------------
create view dbo.sql_view
as select server_name, size_on_disk, total_records from entries where 'SQL' IN(server_name);

Completion time: 2022-10-24T07:39:06.9784300+03:00

Finally, the third method you can use to fetch view definition using T-SQL is the OBJECT_DEFINITION() function.

Example:

select OBJECT_DEFINITION(OBJECT_ID('sql_view')) as view_def;

Resulting output:

view_def
----------------------------------------------------------------------
create view dbo.sql_view
as select server_name, size_on_disk, total_records from entries where 'SQL' IN(server_name);

(1 row affected)

Conclusion

In this post, you discovered how to use the SSMS and T-SQL commands to get view definitions in SQL Server.

Source: linuxhint.com