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.
Tag: views
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.
4 Ways to Get a View’s Definition using Transact-SQL
This article presents 4 ways of using T-SQL to get the definition of a view in SQL Server.
The view definition is the actual T-SQL statement used to create the view.
Get View Information with the VIEWS Information Schema View in SQL Server
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.
Return the Parameters of a Stored Procedure or User-Defined Function in SQL Server (T-SQL Examples)
In SQL Server, you can use the Transact-SQL PARAMETERS system information schema view to find the parameters used in a stored procedure or user-defined function.
More specifically, it returns one row for each parameter of a user-defined function or stored procedure that can be accessed by the current user in the current database.
To use this view, specify the fully qualified name of INFORMATION_SCHEMA.PARAMETERS.
Find the Columns Returned by a Table-Valued Function (T-SQL Examples)
In SQL Server, you can use the Transact-SQL ROUTINE_COLUMNS system information schema view to find the columns returned by a table-valued function.
More specifically, it returns one row for each column returned by the table-valued functions that can be accessed by the current user in the current database.
To use this view, specify the fully qualified name of INFORMATION_SCHEMA.ROUTINE_COLUMNS.
Return Stored Procedures & Functions in a SQL Server Database: ROUTINES (T-SQL Examples)
In SQL Server, you can use the Transact-SQL ROUTINES system information schema view to return a list of stored procedures and functions in the current database.
More specifically, it returns a list of all stored procedures and functions that can be accessed by the current user in the current database.
You can also use ROUTINES simply to return information about a specific procedure or function if required.
To use this view, use the fully qualified name of INFORMATION_SCHEMA.ROUTINES.
How to Return a List of Data Types in SQL Server (T-SQL)
If you ever need to get a list of data types in SQL Server, you can use one of the system views to do just that.
In particular, you can use the sys.types system catalog view. This view returns all system-supplied and user-defined data types defined in the database. If you’re using SQL Server 2000 sys.systypes should do the trick.