Get View Information with the VIEWS Information Schema View in SQL Server

In SQL Server, you can use the Transact-SQL VIEWS system information schema view to return information about one or more views in the current database. It returns one row for views that can be accessed by the current user in the current database.

To use this view, specify the fully qualified name of INFORMATION_SCHEMA.VIEWS.

Example 1 – Return Information about a Specific View

Here’s an example of returning information about all views that the current user has access to in the current database.

SELECT 
  TABLE_CATALOG,
  TABLE_SCHEMA,
  TABLE_NAME,
  CHECK_OPTION,
  IS_UPDATABLE
FROM INFORMATION_SCHEMA.VIEWS;

Result:

+-----------------+----------------+--------------+----------------+----------------+
| TABLE_CATALOG   | TABLE_SCHEMA   | TABLE_NAME   | CHECK_OPTION   | IS_UPDATABLE   |
|-----------------+----------------+--------------+----------------+----------------|
| Music           | dbo            | RockAlbums   | NONE           | NO             |
| Music           | dbo            | JazzAlbums   | NONE           | NO             |
| Music           | dbo            | BluesAlbums  | NONE           | NO             |
+-----------------+----------------+--------------+----------------+----------------+

I intentionally omitted a column in this example. I omitted the VIEW_DEFINITION column. I left it out because it messes up the output when using my command line tool. You can see an example that includes this column below.

Note that the Microsoft documentation warns that you shouldn’t use the INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the schema of a object is to query the sys.objects catalog view.

Example 2 – Return Information about a Specific View

Here’s an example of returning information about a specific view.

SELECT 
  TABLE_CATALOG,
  TABLE_SCHEMA,
  TABLE_NAME,
  CHECK_OPTION,
  IS_UPDATABLE
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'RockAlbums';

Result:

+-----------------+----------------+--------------+----------------+----------------+
| TABLE_CATALOG   | TABLE_SCHEMA   | TABLE_NAME   | CHECK_OPTION   | IS_UPDATABLE   |
|-----------------+----------------+--------------+----------------+----------------|
| Music           | dbo            | RockAlbums   | NONE           | NO             |
+-----------------+----------------+--------------+----------------+----------------+

Here it is again with the VIEW_DEFINITION column included:

SELECT * 
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'RockAlbums';

Result:

+-----------------+----------------+--------------+-------------------+----------------+----------------+
| TABLE_CATALOG   | TABLE_SCHEMA   | TABLE_NAME   | VIEW_DEFINITION   | CHECK_OPTION   | IS_UPDATABLE   |
|-----------------+----------------+--------------+-------------------+----------------+----------------|
| Music           | dbo            | RockAlbums   | CREATE VIEW RockAlbums 
AS
SELECT AlbumName, ArtistName
FROM Albums
        INNER JOIN Artists
        ON Albums.ArtistId = Artists.ArtistId 
    INNER JOIN Genres
    ON Albums.GenreId = Genres.GenreId
WHERE Genres.Genre = 'Rock';                   | NONE           | NO             |
+-----------------+----------------+--------------+-------------------+----------------+----------------+

Example 3 – Return Just the View Definition

Despite the downside of the view definition messing up my layout, this column can come in handy if you’re just looking for the view definition:

SELECT VIEW_DEFINITION 
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'RockAlbums';

Result:

+-------------------+
| VIEW_DEFINITION   |
|-------------------|
| CREATE VIEW RockAlbums 
AS
SELECT AlbumName, ArtistName
FROM Albums
        INNER JOIN Artists
        ON Albums.ArtistId = Artists.ArtistId 
    INNER JOIN Genres
    ON Albums.GenreId = Genres.GenreId
WHERE Genres.Genre = 'Rock';                   |
+-------------------+

Example 4 – Return the View Definitions of Multiple Views

The VIEW_DEFINITION column can be extra handy if want to list the view definitions for multiple views at once:

SELECT VIEW_DEFINITION 
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'RockAlbums';

Result:

+-------------------+
| VIEW_DEFINITION   |
|-------------------|
| CREATE VIEW RockAlbums 
AS
SELECT AlbumName, ArtistName
FROM Albums
        INNER JOIN Artists
        ON Albums.ArtistId = Artists.ArtistId 
    INNER JOIN Genres
    ON Albums.GenreId = Genres.GenreId
WHERE Genres.Genre = 'Rock';                   |
| CREATE VIEW JazzAlbums 
AS
SELECT AlbumName, ArtistName
FROM Albums
        INNER JOIN Artists
        ON Albums.ArtistId = Artists.ArtistId 
    INNER JOIN Genres
    ON Albums.GenreId = Genres.GenreId
WHERE Genres.Genre = 'Jazz';                   |
| CREATE VIEW BluesAlbums 
AS
SELECT AlbumName, ArtistName
FROM Albums
        INNER JOIN Artists
        ON Albums.ArtistId = Artists.ArtistId 
    INNER JOIN Genres
    ON Albums.GenreId = Genres.GenreId
WHERE Genres.Genre = 'Blues';                   |
+-------------------+
(3 rows affected)

Example 5 – Returning Large View Definitions

The VIEW_DEFINITION column has a maximum length of nvarchar(4000). For view definitions larger than this, you can use the OBJECT_DEFINITION() function (along with the OBJECT_ID() function) to return the full definition.

The return value of OBJECT_DEFINITION() function is nvarchar(max), so it doesn’t have the character limitation of the VIEW_DEFINITION column (which as mentioned, is nvarchar(4000)).

Example:

SELECT 
  OBJECT_DEFINITION(OBJECT_ID(TABLE_NAME))
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'RockAlbums';

Result:

+--------------------+
| (No column name)   |
|--------------------|
| CREATE VIEW RockAlbums 
AS
SELECT AlbumName, ArtistName
FROM Albums
        INNER JOIN Artists
        ON Albums.ArtistId = Artists.ArtistId 
    INNER JOIN Genres
    ON Albums.GenreId = Genres.GenreId
WHERE Genres.Genre = 'Rock';                    |
+--------------------+

Obviously, this example doesn’t show the benefit of using the OBJECT_DEFINITION() function, because the view definition is too small, but if you have an extra large view definition, hopefully this example helps.