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.