Track and Monitor SQL Server View Usage

Problem

I recently was tasked with tracking and monitoring views in the SQL Server databases that I administer.  Among the attributes that I am required to track are code module name, defining T-SQL for a module, created and last modified dates, and last run datetime.  Please demonstrate how to achieve these goals with T-SQL.

Solution

This tip is the third in a collection of tips on tracking and monitoring code modules.  The first two tips focused, respectively, on stored procedures and user-defined functions.  This tip demonstrates how to perform the same tracking and monitoring tasks for views.

Views are a useful object for returning the contents of a select statement as a virtual table.  MSSQLTips.com previously published a quick and easy-to-read introduction to views.

In this tip, you will learn how to:

• List the views in a database by schema
• Display T-SQL that defines what a view does
• Retrieve the initial creation date and the last date when a view was modified
• Compute how long it is between two successive runs of a cached view

List SQL Server Views

Three system views allow you to list user-defined views; these are sys.views, sys.objects, and information_schema.views.

• The sys.objects object is a generalized system view for tracking and monitoring many kinds of SQL Server user-defined objects, such as views, stored procedures, and user-defined functions.  When a SQL Server user-defined object, such as view, has the same name in two different schemas, sys.objects distinguishes between them by their object_id value.  You can examine this SQL Docs page for a list of all the SQL Server object types tracked by sys.objects.
• The sys.views object is a specialized system view dedicated to tracking and monitoring SQL Server views.  This system view performs a role for views that is comparable to the sys.procedures object for stored procedures.
• The term information_schema applies to a collection of system views that supports cross-platform database solutions.  These views are ANSI (American National Standards Institute) compliant.  There are different types of information_schema views depending on the type of object that you want to track.  For example, information_schema.views is the name of the view for tracking views originally defined by users with a create view statement.

All three system views mentioned above can track objects in the current database.  If you change the current database with a use statement, then the system views will return user-defined views for the new current database.  Additionally, the information_schema views can track objects in a database that is different from the current database.  To accomplish this, precede information_schema with the name of a non-current database.  The syntax for referencing views in another database than the current one is:

SELECT * FROM non_current_database_name.information_schema.views

The following script shows how simple it is to enumerate view names in a database with each of the preceding three systems views.

• The first code block makes CodeModuleTypes the default database.  This database has been used in several prior tips on stored procedures, user-defined functions, and views.  There are several user-defined views in the CodeModuleTypes database from two upcoming tips.
• The next code segment lists views in the CodeModuleTypes database from sys.views and two built-in SQL Server functions.  The view identifiers are derived with the help of a select statement.
  • The db_name() function returns the name of the default database context.  An alias of catalog_name is assigned to the return value from the function.
  • The schema_name() function returns the name for a schema based on the schema_id within a row of sys.views.  The alias for the return value from the schema_name() function is schema_name.  SQL Server applications can have identically named views in different schemas.
  • The name column value from sys.views contains the name of a view within a schema of the default database.
  • The source column is a constant in the results set from the select statement.  For this code block, the column value equals the string value of sys.views.
  • The argument for the from clause is specified as sys.views.
• The third code segment illustrates how to list view names from sys.objects.
  • Because sys.objects has a separate row for many different kinds of SQL Server objects, it is necessary to include a where clause that specifies the type of object for which you seek names.  You can use either the type column value or the type_desc column value.  The code segment below uses a where clause criterion of type_desc equals ‘view’.  You can replace the type_desc criterion with type equals ‘v’; the string value of v for the type column value is a code for a view.
  • The database name and schema name for the current row in the sys.objects system view are derived respectively with the  db_name() function and schema_name() function.
  • The name column in sys.objects denotes the name of an object for a row in sys.objects.  Because of the where clause criterion, the name column value will always point to a view.
• The fourth code segment demonstrates the syntax for enumerating the views in a database with the information_schema.view system view.
  • The information_schema.view object has a separate row for each view in a database.
  • The information_schema.view object also has a separate column for the database name and schema name as well as the view name.
    • Therefore, there is no need to invoke the db_name() to derive the current database name nor the schema_name() function to return the name of a schema in which a view resides.
    • The columns for database, schema, and view names all start with a prefix of table_.  This reinforces the notion that a view acts like a virtual table.

USE CodeModuleTypes
GO
-- for enumerating views
-- list all views from sys.views catalog view
SELECT
  DB_NAME() catalog_name,
  SCHEMA_NAME(schema_id) schema_name,
  name view_name,
  'sys.views' source
FROM sys.views
-- list all views from sys.ojects catalog view
SELECT
  DB_NAME() catalog_name,
  SCHEMA_NAME(schema_id) schema_name,
  name view_name,
  'sys.objects' source
FROM sys.objects
WHERE type_desc = 'view'

-- list all views from information_schema.views
SELECT
  table_catalog catalog_name,
  table_schema schema_name,
  table_name view_name,
  'information.schema.views' source
FROM information_schema.views

Here’s the output from the preceding script.

• There are three panes.  Each pane displays the results set for listing view names based on a different system view.  The source column provides the name of the system view providing the listing of view names.
• Because of alias assignments, the column names are the same in all three panes.  All three built-in SQL Server objects (sys.views, sys.objects, and information_schema.views) depend on the order of objects in sys.objects.
  • The first column denotes the default database (catalog) name.
  • The second column shows the schema name in which the views reside.  The second column value is always dbo – just to keep things simple in terms of schema.
    • The third column denotes the view name.  Notice that the order of the view names is the same in all three panes. 
      • The view names do not appear in alphabetical order.
      • Instead, they appear in object_id order.  You can determine the object_id value for views by including the object_id as a column in the results sets from sys.objects.

SQL Server View Code

You can extract the defining T-SQL for a view with a select statement from either sys.views or information_schema.views.  While there is a cross-platform advantage when working with information_schema.views, the overall benefit of a cross-platform approach diminishes as your needs for meta data grow more complex because proprietary system views, such as sys.views, offer much more meta data and related kinds of information for tracking and monitoring SQL Server objects.  For this reason, this tip features an approach for extracting defining T-SQL code with sys.views.  Just keep in mind that there is an alternative approach using information_schema.views in case your requirements call for a cross-platform solution.

The code for this section, along with all the other sections in this tip, assumes the default database is CodeModuleTypes.  The following script returns the defining T-SQL code for views in the default database.  Views are identified by their three-part names (database or catalog name, schema name, and view name).  The built-in object_definition function can return the defining T-SQL code based on the object_id value of a row in sys.views.  Recall that sys.views has a separate row for each view in a database.  The object_definition function in the script below returns a create view statement for the object_id value on each row within sys.views.

-- extract T-SQL definition for each view in a database
-- from sys.views
SELECT
  DB_NAME() catalog_name,
  SCHEMA_NAME(schema_id) schema_name,
  name view_name,
  OBJECT_DEFINITION(object_id) [T-SQL Definition]
FROM sys.views

Here’s the output from the preceding script.  Each T-SQL Definition column value contains a create view statement.  A create view statement is returned whether the last definition of the view was with a create view statement or an alter view statement.  For your easy reference, the create view term in each line is enclosed within a red box.  The catalog, schema, and view names are truncated (if necessary) to allow room to show the create view term for each row in the results set from the preceding query.

You can select the contents of any T-SQL Definition column value and copy it to the Windows clipboard.  You can then paste its code to a new query tab or any empty space in the current query tab within SQL Server Management Studio.  The following screen shot shows the outcome of pasting the create view statement from the first row of the preceding screen shot to an empty query tab.  The query code can also include comment lines from the last time the code was modified via an alter view statement.  When copied to query tab, the code automatically appears in a more readable and editable format than as a cell entry in the T-SQL Definition column.  This reformatting of the code makes it much easier to read, understand, and modify (if necessary).

List the SQL Server View Create and Last Modified date times

Two aspects of a view that you can track are when it was initially created and when it was last modified.  You create a view with a create view statement.  You modify a previously created view with an alter view statement.  The alter view statement does not change the create_date column value in sys.objects, but it does update the modify_date column value.  Create_date and modify_date columns appear in both sys.objects and sys.views.  The following script shows how to query sys.views to derive datetime values for when a view was initially created and when it was last modified.

• The script starts by creating a fresh copy of dbo.v_employees which returns three columns from the dbo.employees table in the CodeModuleTypes database.
• After the create view statement, the script invokes a waitfor delay function to pause the script’s operation for one minute.
• Then, it alters the initial version of dbo.v_employees to select just a subset of the columns from the dbo.employees table.
• Finally, the script queries sys.views to extract the create_date column value for a view.  The final query also displays the modify_date column value from sys.views for when a view was last altered.  Although the create_date and modify_date column names suggest a date data type, the values return with a datetime data type.
• A where clause criterion restricts the results set to a single row – namely,  for the dbo.v_employees view.

-- demonstrates ability to show create and last modified datetimes
-- for a view named v_employees
-- conditionally drop a stored proc
IF OBJECT_ID('dbo.v_employees') IS NOT NULL
  DROP VIEW dbo.v_employees
GO
-- create a new view
CREATE VIEW dbo.v_employees
AS
SELECT
  BusinessEntityID,
  OrganizationLevel,
  JobTitle,
  'this is the original version of the view' comment
FROM dbo.employees
GO
-- wait a minute
WAITFOR DELAY '00:01:00.000'
GO
-- alter a view
-- do not include the OrganizationLevel column
ALTER VIEW dbo.v_employees
AS
SELECT
  BusinessEntityID
  --,OrganizationLevel
  ,
  JobTitle,
  'this is the altered version of the view' comment
FROM dbo.employees
GO
-- display create datetime and last modified datetime
-- for v_employees's full name (db_name, schema_name, name)
-- and difference minutes between create_date and modify_date
SELECT
  DB_NAME() catalog_name,
  SCHEMA_NAME(schema_id) schema_name,
  name view_name,
  create_date,
  modify_date,
  DATEDIFF(mi, create_date, modify_date) [difference in minutes]
--,object_definition(object_id) [T-SQL Definition]
FROM sys.views
WHERE name = 'v_employees'

Here’s the results set from the preceding script.

• The view_name column corresponds to the name criterion in the select statement for sys.views.
• In addition to view_name, a view object is identified by the database and the schema in which it resides.
• The datediff function reports a one-minute difference between the create_date column and the modify_date column values.  This gap corresponds to the waitfor delay function argument.

When did a SQL Server view last run?

One of the most interesting tracking tasks might be how to determine the difference in start times between two successive invocations of a view.  Because a view serves as a virtual table, views are mostly likely to be invoked within the from clause of a select statement.  However, any two successive invocations of a view can use the same view in different ways.  For example, one invocation of a view could be from a select statement with a where clause, and the next invocation of the same view could be from a select statement without a where clause. 

A query invoking a view can operate from a query batch.

• The sys.dm_exec_query_stats system view can return the last time that a query batch was invoked.
• The sys.dm_exec_sql_text system view can return the text for a query batch.
• Within SQL Server Management Studio, you can define a batch for a select statement by invoking a view with go keywords before and after the select statement.

The following script illustrates how to apply the preceding three design guidelines for assessing the number of seconds between two successive invocations of a view named employee_persons in the dbo schema of the CodeModuleTypes database.  The script assumes that a use statement was issued previously to denote CodeModuleTypes as the default database for the script.

• The script starts by creating a global temp table named ##execution_times with four columns.  These columns are for storing the start time and query text for the first and second invocations of a view.
  • The first_time column has a datetime data type.  It is used to store the start time for the first invocation of the employee_persons view.
  • The first_text column has a nvarchar data type.  It is used to store the query statement for the first invocation of the employee_persons view.
  • The second_time column has a datetime data type.  It is used to store the start time for the second invocation of the employee_persons view.
  • The second_text column has a nvarchar data type.  It is used to store the query statement for the second invocation of the employee_persons view.
• The next code segment shows a select statement that invokes the employee_persons view.  The select statement is preceded and followed by go keywords so that the select statement that invokes the view resides in a batch by itself.  This code block contains the first invocation of the employee_persons view.
• The next code block is an insert into statement for the ##execution_times global temp table.  The select statement in the insert into statement cross applies the text from a query batch that invokes the employee_persons view with the last_execution_time for the query batch.  The insert into statement populates the first_time and first_text columns of the first row in the ##execution_times global temp table.  Although the query text is not used in this timing demonstration, the query text can be useful in broader contexts.
• Then, a waitfor delay statement pauses the operation of the script for one minute.
• The next batch after the waitfor delay statement invokes the employee_persons view a second time.  However, the select statement for the second invocation of the employee_persons view does not include a where clause.
• Then, another code  block for an insert into statement populates the second_time and second_text columns of the second row within the ##execution_times global temp table.
• The script concludes with a code segment that merges the two rows of ##execution_times into a single row.  A datediff function computes the difference in seconds between first_time and second_time column values.

-- display run-times demonstration for a select from dbo.employee_persons view
-- create global temp table for storing queries and their execution times
IF OBJECT_ID('tempdb..##execution_times') IS NOT NULL
BEGIN
  DROP TABLE ##execution_times
END
GO
CREATE TABLE ##execution_times (
  first_time datetime,
  first_text nvarchar(4000),
  second_time datetime,
  second_text nvarchar(4000)
)
-- first execution of select from view
GO
SELECT
  *
FROM dbo.employee_persons
WHERE OrganizationLevel IS NULL
OR OrganizationLevel = 1
GO
INSERT INTO ##execution_times (first_time, first_text)
  -- display first view run-time and query statement
  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 dbo.employee_persons%'
-- wait a minute
WAITFOR DELAY '00:01:00.000'
-- second execution of select from view
GO
SELECT
  *
FROM dbo.employee_persons
GO
INSERT INTO ##execution_times (second_time, second_text)
  -- display second view run-time and query statement
  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 dbo.employee_persons%'
-- merge first and second execution times into one row
-- then use datediff function to compute difference in execution times
SELECT
  first_time,
  second_time,
  DATEDIFF(ss, first_time, second_time) [difference in seconds]
FROM (SELECT
  first_time,
  LEAD(second_time, 1) OVER (ORDER BY second_time) second_time
FROM ##execution_times) first_and_second_times_on_one_row
WHERE DATEDIFF(ss, first_time, second_time) IS NOT NULL

Here’s an excerpt from the output for the preceding script.  The excerpt contains three panes.

• The first pane shows the results set from the first invocation of employee_persons.
• The second pane shows an excerpt from the results set from the second invocation of employee_persons.
• The third pane shows the datetime values for first_time and second_time as well as the difference in seconds between the two datetime values.  The difference is 60 seconds,  which matches the pause in the script’s operation programmed by the waitfor delay statement between the first invocation of the view and the second invocation of the view.  The first view runs so quickly that its impact on the time between the start of the first and second invocations of the view is less than one second.

Next Steps

• You can try out the code examples for this tip with the views and tables added to the CodeModuleTypes database in two prior tips on views.  The “Listing the view names in a database” section in this tip has links to both prior tips.  The code examples in this tip list the names and other view attributes from the views created in both prior tips.
• After you have created the views and their underlying base tables in the CodeModuleTypes database, test and modify the scripts in this tip that address the kinds of issues which you want to explore.  Examine the output from the scripts you select to verify the code performs as described.
• Finally, modify the script(s) to reference another source database with selected views and tables that follow from your requirements.