Retrieving SQL View Definitions

I recently faced a situation where I had to track down the definition of a particular SQL view for various databases. I didn’t want to click through each database in SSMS to gather the information. I thought I would write a query that I could use to save some clicks and gather what I needed for each database.

The first part of that task, querying for the view definition, may be a bit tricky. I would venture to guess it may not be in the first couple of places you would think to look. Let’s walk through how we can use a query to retrieve the definition of a view.

Checking sys.views and sys.all_views

For this example, I want the definition of a view I created in StackOverflow2013 called OhioUsers. Where do we start? Your initial thought might be to check sys.views. Given the name, that seems to indicate it’s a good place to check. While it contains helpful information, if we query sys.views, we’ll only see this in the first few columns and no view definition:

SELECT * FROM sys.views;
GO

That has some view information, but I think I need all of the view information. With that said, what about checking sys.all_views? Does that give us the view definition?

SELECT * FROM sys.all_views;
GO

That gives us information about all of the views, but still not the view definition. If we change our query above to only look for the OhioUsers view, we’ll see similar results to the first query against sys.views:

View Definition in sys.sql_modules

Instead, we need to check sys.sql_modules. This is where we can find more information on a variety of object types (P, RF, V, TR, FN, IF, TF, and R). Let’s see if we can grab the OhioUsers view definition:

SELECT definition
FROM sys.sql_modules  
WHERE object_id = OBJECT_ID('OhioUsers');   
GO

There it is. sys.sql_modules contains the view definition.

Interestingly, I renamed the view in the process of writing this blog post and just noticed that the view definition contains the old “MyOhioTestView” name instead of the new “OhioUsers” view name. I’ll have to look further into that.

Looking for Changes

We checked sys.views and sys.all_views for the view definition but came up empty. We needed to query sys.sql_modules to find the view definition we were after.

Thanks for reading!