Views in SQL Server are a powerful tool for returning the contents of a select statement as a virtual table. As a database administrator, it is important to track and monitor views in order to ensure their performance and integrity. In this article, we will explore how to achieve these goals using T-SQL.

List the Views in a Database

There are three system views in SQL Server that allow you to list user-defined views: sys.views, sys.objects, and information_schema.views. The sys.views object is a specialized system view dedicated to tracking and monitoring SQL Server views. The sys.objects object is a generalized system view that can track many kinds of user-defined objects, including views. The information_schema.views view is ANSI compliant and can track views originally defined by users with a create view statement.

Here is an example of how to list the views in a database using each of these system views:

SELECT
  DB_NAME() AS catalog_name,
  SCHEMA_NAME(schema_id) AS schema_name,
  name AS view_name,
  'sys.views' AS source
FROM sys.views

SELECT
  DB_NAME() AS catalog_name,
  SCHEMA_NAME(schema_id) AS schema_name,
  name AS view_name,
  'sys.objects' AS source
FROM sys.objects
WHERE type_desc = 'view'

SELECT
  table_catalog AS catalog_name,
  table_schema AS schema_name,
  table_name AS view_name,
  'information_schema.views' AS source
FROM information_schema.views

Retrieve the T-SQL Definition of a View

To extract the defining T-SQL code for a view, you can use the object_definition function with the object_id value of the view. Here is an example:

SELECT
  DB_NAME() AS catalog_name,
  SCHEMA_NAME(schema_id) AS schema_name,
  name AS view_name,
  OBJECT_DEFINITION(object_id) AS [T-SQL Definition]
FROM sys.views

Track the Create and Last Modified Dates of a View

You can track when a view was initially created and when it was last modified by querying the create_date and modify_date columns in the sys.views system view. Here is an example:

SELECT
  DB_NAME() AS catalog_name,
  SCHEMA_NAME(schema_id) AS schema_name,
  name AS view_name,
  create_date,
  modify_date
FROM sys.views
WHERE name = 'view_name'

Determine the Time Between Two Successive Runs of a View

If you want to determine the time between two successive invocations of a view, you can use the sys.dm_exec_query_stats system view to get the last execution time of a query batch. Here is an example:

CREATE TABLE ##execution_times (
  first_time datetime,
  first_text nvarchar(4000),
  second_time datetime,
  second_text nvarchar(4000)
)

-- First execution of the view
SELECT
  *
FROM view_name
WHERE condition

INSERT INTO ##execution_times (first_time, first_text)
  SELECT
    qs.last_execution_time,
    qt.text
  FROM sys.dm_exec_query_stats qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
  WHERE qt.text LIKE 'select * from view_name%'

-- Wait for a period of time

-- Second execution of the view
SELECT
  *
FROM view_name

INSERT INTO ##execution_times (second_time, second_text)
  SELECT
    qs.last_execution_time,
    qt.text
  FROM sys.dm_exec_query_stats qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
  WHERE qt.text LIKE 'select * from view_name%'

-- Merge the execution times and calculate the difference
SELECT
  first_time,
  second_time,
  DATEDIFF(ss, first_time, second_time) AS [difference in seconds]
FROM (SELECT
  first_time,
  LEAD(second_time, 1) OVER (ORDER BY second_time) AS second_time
FROM ##execution_times) AS first_and_second_times_on_one_row
WHERE DATEDIFF(ss, first_time, second_time) IS NOT NULL

By using these techniques, you can effectively track and monitor views in your SQL Server databases. This will help you ensure their performance and make informed decisions about their usage.