Get the Underlying Columns of a View Based on its Result Set

Posted on by Ian

One of the great things about database views, is that they allow you to run complex queries without needing to know the underlying database schema.

Yes, it’s true that you need to know the underlying schema when you create the view, but you only need to do that once. Once you’ve created it, you can query that view all day long without needing to remember all the table and column names, etc.

Views typically combine data from multiple tables into a single, virtual table, which makes it kind of like a “black box”. As long as it works as designed, you don’t need to concern yourself with the hidden details.

But what if you do want to check a view for its underlying tables and columns?

While the sp_help system stored procedure will provide you with information about the columns returned by the view, it doesn’t provide information about the columns in the base tables that are referenced in the view.

And yes, there are many ways to check the actual definition of the view. But if it’s a large view, you run the risk of going cross-eyed, just trying to pick out all the actual base tables involved.

However, there’s another method you can use to return the base tables and columns used by a view.

You can use the sys.dm_exec_describe_first_result_set system dynamic management function to return metadata about the result set when you query the view.

The way it works is, you pass a T-SQL query to the function, and it will return metadata about the result set. In this case, the query that you pass to the function would be the query that you’d use when querying the view.

One benefit of using this method is that you get the base table and column information in a nice list. Each column is listed in a separate row.

Also, you can narrow the results by refining your query, which means that you can eliminate any irrelevant columns (i.e. columns that are in the view, but are not relevant for your specific query).

Example

Here’s an example to demonstrate how it works.

SELECT 
    CONCAT(
        source_server + '.', 
        source_database + '.', 
        source_schema + '.', 
        source_table + '.', 
        source_column) AS [Source Column],
    name AS [View Column],
    user_type_name,
    system_type_name,
    max_length,
    [precision],
    scale
FROM sys.dm_exec_describe_first_result_set(
    N'SELECT * FROM vAllCats', 
    NULL, 
    1
);

Result:

+-----------------------+---------------+------------------+--------------------+--------------+-------------+---------+
| Source Column         | View Column   | user_type_name   | system_type_name   | max_length   | precision   | scale   |
|-----------------------+---------------+------------------+--------------------+--------------+-------------+---------|
| Test.dbo.Cats.CatId   | CatId         | NULL             | int                | 4            | 10          | 0       |
| Test.dbo.Cats.CatName | CatName       | NULL             | varchar(60)        | 60           | 0           | 0       |
+-----------------------+---------------+------------------+--------------------+--------------+-------------+---------+

Here, I’ve decided to use the CONCAT() function to concatenate multiple column names in order to make it easier to visualise the schema.

In this case, the Source Column and the View Column” (i.e. the column returned by the view) both share the same name. This will happen if the view doesn’t use an alias for the column.

Note that the reason we are able to get the source columns, tables, etc, is because we use 1 as the third argument. When we use this value, each query is analysed as if it has a FOR BROWSE option on the query.

When the View uses Column Aliases

If the view uses column aliases that are different to the actual underlying column names, then that will be reflected our results.

In this example, we query a view that uses column aliases.

SELECT 
    CONCAT(
        source_server + '.', 
        source_database + '.', 
        source_schema + '.', 
        source_table + '.', 
        source_column) AS [Source Column],
    name AS [View Column],
    user_type_name,
    system_type_name,
    max_length,
    [precision],
    scale
FROM sys.dm_exec_describe_first_result_set(
    N'SELECT * FROM vAlbums', 
    NULL, 
    1
);

Result:

+------------------------------------+---------------+------------------+--------------------+--------------+-------------+---------+
| Source Column                      | View Column   | user_type_name   | system_type_name   | max_length   | precision   | scale   |
|------------------------------------+---------------+------------------+--------------------+--------------+-------------+---------|
| Homer.Music.dbo.Artists.ArtistName | Artist        | NULL             | nvarchar(255)      | 510          | 0           | 0       |
| Homer.Music.dbo.Albums.AlbumName   | Album         | NULL             | nvarchar(255)      | 510          | 0           | 0       |
| Homer.Music.dbo.Genres.Genre       | Genre         | NULL             | nvarchar(50)       | 100          | 0           | 0       |
| Homer.Music.dbo.Artists.ArtistId   | ArtistId      | NULL             | int                | 4            | 10          | 0       |
| Homer.Music.dbo.Albums.AlbumId     | AlbumId       | NULL             | int                | 4            | 10          | 0       |
| Homer.Music.dbo.Genres.GenreId     | GenreId       | NULL             | int                | 4            | 10          | 0       |
+------------------------------------+---------------+------------------+--------------------+--------------+-------------+---------+

If we look at the first two rows, we can see that the underlying columns (returned by the source_column columns), are different to the “View Column” (returned by the name column).

We can also see that the source columns for this view are located on a linked server called “Homer”.

Another thing to note is that when using browse mode like we are here (i.e. with 1 as the third argument), we also get other columns that are involved in completing the query (ArtistId, AlbumId, and GenreId), even though they aren’t actually returned in the result set.

Refine the Query

One of the things that differentiates sys.dm_exec_describe_first_result_set from procedures like sp_help and sp_helptext, is that it describes the result set not the view.

The results you get will depend on the actual query you pass, not just the view.

Here’s the same query as in the previous example, except this time I select just one column from the view (instead of using the * wildcard to select all columns).

SELECT 
    CONCAT(
        source_server + '.', 
        source_database + '.', 
        source_schema + '.', 
        source_table + '.', 
        source_column) AS [Source Column],
    name AS [View Column],
    user_type_name,
    system_type_name,
    max_length,
    [precision],
    scale
FROM sys.dm_exec_describe_first_result_set(
    N'SELECT Album FROM vAlbums', 
    NULL, 
    1
);

Result:

+----------------------------------+---------------+------------------+--------------------+--------------+-------------+---------+
| Source Column                    | View Column   | user_type_name   | system_type_name   | max_length   | precision   | scale   |
|----------------------------------+---------------+------------------+--------------------+--------------+-------------+---------|
| Homer.Music.dbo.Albums.AlbumName | Album         | NULL             | nvarchar(255)      | 510          | 0           | 0       |
| Homer.Music.dbo.Artists.ArtistId | ArtistId      | NULL             | int                | 4            | 10          | 0       |
| Homer.Music.dbo.Albums.AlbumId   | AlbumId       | NULL             | int                | 4            | 10          | 0       |
| Homer.Music.dbo.Genres.GenreId   | GenreId       | NULL             | int                | 4            | 10          | 0       |
+----------------------------------+---------------+------------------+--------------------+--------------+-------------+---------+

So this time, only get four rows are returned instead of six.

Get the Underlying Columns from Multiple Views

As mentioned, the sys.dm_exec_describe_first_result_set function describes the whole result set, not just a single view or other object.

Therefore, you can discover the underlying columns from multiple views and objects all in one go.

Example:

SELECT 
    CONCAT(
        source_server + '.', 
        source_database + '.', 
        source_schema + '.', 
        source_table + '.', 
        source_column) AS [Source Column],
    name AS [View Column],
    user_type_name,
    system_type_name,
    max_length,
    [precision],
    scale
FROM sys.dm_exec_describe_first_result_set(
    N'SELECT * FROM vAllCats c INNER JOIN vAllDogs d ON c.CatName = d.DogName', 
    NULL, 
    1
);

Result:

+-----------------------+---------------+------------------+--------------------+--------------+-------------+---------+
| Source Column         | View Column   | user_type_name   | system_type_name   | max_length   | precision   | scale   |
|-----------------------+---------------+------------------+--------------------+--------------+-------------+---------|
| Test.dbo.Cats.CatId   | CatId         | NULL             | int                | 4            | 10          | 0       |
| Test.dbo.Cats.CatName | CatName       | NULL             | varchar(60)        | 60           | 0           | 0       |
| Test.dbo.Dogs.DogId   | DogId         | NULL             | int                | 4            | 10          | 0       |
| Test.dbo.Dogs.DogName | DogName       | NULL             | nvarchar(255)      | 510          | 0           | 0       |
| Test.dbo.Dogs.GoodDog | GoodDog       | NULL             | bit                | 1            | 1           | 0       |
+-----------------------+---------------+------------------+--------------------+--------------+-------------+---------+