Microsoft SQL Server makes it very easy to create and use Views within SQL Server Management Studio.
In this very brief tutorial, we’ll discuss how easy it is to see the definition of a View after it has been created and saved in your database.
If you need a full rundown on what a View is, you should check out my full tutorial on the topic, found here:
What is a View in SQL Server? A Guide for Beginners
Let’s get into it:
How to see the definition of a View in SQL Server:
There are two ways you can see the definition of a View in SQL Server. The first method we’ll discuss is how you can very simply see the definition of a View that you aren’t interested in making any changes to.
Method # 1: Use the SP_HELPTEXT system stored procedure
There is a built-in system stored procedure called SP_HELPTEXT that very simply prints out the definition of an object you specify that exists within your database. It is very easy to use.
First, we need to open a new query window for the database our View is within. You can find the database within the Object Explorer. If you don’t have the Object Explorer already pulled up when you open SQL Server management studio, you can open it by navigating to View | Object Explorer, like so:
We’ll be working with a database called SimpleSQLTutorials. Here’s that database seen in my Object Explorer:
We can open a new query window for this database by right-clicking it and choosing New Query, like so:
In the query window that appears, simply type out the statement EXECUTE SP_HELPTEXT <View name>
Here’s an example of using SP_HELPTEXT to print out the definition of a View called DistinctLastNames:
That’s it! As you can see, this particular View is very short and simple.
The SP_HELPTEXT system stored procedure can be used to see the definition of user defined stored procedures and user defined functions also. It’s a very useful tool to know!
Notice when we use SP_HELPTEXT to see the definition of a View, we cannot make changes to the View. If we want to see the definition of the View and maybe make changes to it, we need to open the definition of the View a different way.
Method # 2: Seeing the definition of the View in an ALTER VIEW window
If we want to see the definition of the View and maybe make changes to it, we can use the Object Explorer to open the View in an ALTER VIEW window.
First, we expand the tree for our database and navigate to ‘Views‘ like so:
Of course, below the Views tree is all the Views that have been created in this database. I only have one View called DistinctLastNames.
Then, we right click the View for which we want to see the definition and navigate to Script View as | ALTER to | New Query Editor Window
Here’s an example:
This will open the definition of the View in a new query window. It will be ready for you to make changes via the ALTER VIEW statement:
If you need to make changes to the View, you simply make those changes and execute!
Next Steps:
Leave a comment if you found this tutorial helpful!
I hope you enjoyed this very brief tutorial on Views. If you found it helpful, you should consider downloading the following FREE Ebook:
FREE Ebook on SQL Server Table Expressions!
This Ebook provides an excellent summary of everything you need to know about the different table expressions we can create in Microsoft SQL Server, including Views. It will be a great resource for you to reference throughout your career as a data professional. Make sure you download it today!
As I mentioned earlier, I also have a full beginner-friendly introduction to Views for you to review. It’s found here:
What is a View in SQL Server? A Guide for Beginners
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, leave a comment. Or better yet, send me an email!
