{"id":893,"date":"2018-10-13T10:23:42","date_gmt":"2018-10-13T03:23:42","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=893"},"modified":"2020-04-11T20:13:08","modified_gmt":"2020-04-11T13:13:08","slug":"sql-server-get-view-information","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-views\/sql-server-get-view-information\/","title":{"rendered":"How to Get Information About a View in SQL Server"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn various ways to get the information of a view in a SQL Server Database.<\/p>\n\n\n\n

Getting the view information using the sql.sql_module catalog #<\/a><\/h2>\n\n\n\n

To get the information of a view, you use the system catalog sys.sql_module<\/code> and the OBJECT_ID()<\/code> function:<\/p>\n\n\n

SELECT<\/span>\n    definition,\n    uses_ansi_nulls,\n    uses_quoted_identifier,\n    is_schema_bound\nFROM<\/span>\n    sys.sql_modules\nWHERE<\/span>\n    object_id\n    = object_id(\n            'sales.daily_sales'<\/span>\n        );\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this query, you pass the name of the view to the OBJECT_ID()<\/code> function in the WHERE<\/code> clause. The OBJECT_ID()<\/code> function returns an identification number of a schema-scoped database object.<\/p>\n\n\n\n
Here is the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
Note that you need to output the result to the text format in order to see the SELECT<\/code> statement clearly as the above picture.<\/p>\n\n\n\n
To show the results as text, from the query editor, you press Ctrl-T<\/strong> keyboard shortcut or click the Results to Text<\/strong> button as shown in the following screenshot:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
Getting view information using the sp_helptext stored procedure #<\/a><\/h2>\n\n\n\n
The sp_helptext<\/code> stored procedure returns the definition of a user-defined object such as a view.<\/p>\n\n\n\n
To get a view’s information, you pass the view name to the sp_helptext<\/code> stored procedure. For example, the following statement returns the information of the sales.product_catalog<\/code> view:<\/p>\n\n\n
EXEC sp_helptext 'sales.product_catalog' ;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following picture shows the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
Getting the view information using OBJECT_DEFINITION() function #<\/a><\/h2>\n\n\n\n
Another way to get the view information is to use the OBJECT_DEFINITION()<\/code> and OBJECT_ID()<\/code> functions as follows:<\/p>\n\n\n
SELECT<\/span> \n    OBJECT_DEFINITION(\n        OBJECT_ID(\n            'sales.staff_sales'<\/span>\n        )\n    ) view_info;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following picture shows the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
In this tutorial, you have learned how to various ways to get the information about a view in SQL Server Database.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t