Below are four ways to list out the views in a MySQL database using SQL or the command line.
views
Get the Underlying Columns of a View Based on its Result Set
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?
How to Convert a MongoDB View to a Collection
If you have a view in a MongoDB database that you’d prefer to be a collection, you’re in the right place.
Below is an example of converting a view to a collection in MongoDB.
How to Encrypt a View in SQL Server
In SQL Server, you can encrypt a view at the time you first create it, or you can alter it later to include encryption.
To create a view with T-SQL, you use the CREATE VIEW syntax. To encrypt it, you add the WITH ENCRYPTION argument.
You can also use the same argument to encrypt an existing view when using ALTER VIEW.
Remove SCHEMABINDING from a View in SQL Server
The purpose of schema binding a view is to ensure that the base tables referenced in the view cannot be modified in a way that would affect the view definition.
This is normally a good thing. After all, you don’t want someone coming along and dropping a table that your view depends on, do you?
But what if you need to make changes to one or more tables referenced by your view?
Create a Schema Bound View in SQL Server
It’s usually a good idea to schema bind your views in SQL Server.
Schema binding your view will ensure that the underlying tables can’t be changed in a way that would affect the view. Without schema binding, the underlying tables or other objects could be modified or even deleted. If that happens, the view will no longer work as expected.
Difference Between sys.views, sys.system_views, & sys.all_views in SQL Server
Three of the system catalog views in SQL Server include sys.views, sys.system_views, and sys.all_views.
These three catalog views all provide metadata about views in the database, but there’s a subtle difference between each view.
4 Ways to List All Views in a SQL Server Database
This article presents four ways to return a list of user-defined views in a SQL Server database.
If you want to see only system views, or both user-defined and system views, see Difference Between sys.views, sys.system_views, & sys.all_views in SQL Server.
Return Operating System Version Info in SQL Server with the sys.dm_os_host_info Dynamic Management View
In SQL Server, you can use the sys.dm_os_host_info system dynamic management view to return information about the underlying operating system.
This view is similar to the sys.dm_os_windows_info view, except that sys.dm_os_host_info handles Linux systems as well as Windows, and it has more columns to differentiate between the two.
Use OBJECTPROPERTY() to Determine Whether an Object is a View in SQL Server
You can use the OBJECTPROPERTY() function in SQL Server to find out whether or not an object is a view.
This function accepts two parameters: the object ID, and the property for which you’re checking it for.
Therefore, you can pass the object ID as the first argument, and IsView as the second, and the function will return either a 1 or a 0 depending on whether or not it’s a view.
A return value of 1 means that it is a view, and a value of 0 means that it’s not.