I won’t present the results here because it’s a long list. You can also narrow it down to just a given owner or catalog.Įxecuting the following code will return all views in the current database – including system views. You can narrow the results to just views with the parameter. This includes any table or view, except synonym objects. The sp_tables stored procedure returns a list of objects that can be queried in the current environment. Option 4 – The sp_tables Stored Procedure If you want to return each view’s definition, you can join it with the sys.sql_modules system view. The sys.objects view doesn’t include a column for the object’s definition. Another way to do this is filter by the type_desc column: SELECT Option 3 – The sys.objects System Catalog ViewĪnother way to return a list of views is to query the sys.objects system catalog view. The sys.view view doesn’t include a column for the object’s definition. This effect applies to all tables the query accesses through its JOIN clauses. When used as a substitute for explicit column names, it returns all columns in all tables that a query is selecting FROM. Option 2 – The sys.views System Catalog ViewĪnother way to return a list of views is to query the sys.views system catalog view. Simple select statement is the wildcard character used to select all available columns in a table. The INFORMATION_SCHEMA.VIEWS view also has a VIEW_DEFINITION column, so you can easily return each view’s definition if required. You can use the VIEWS information schema view to get a list of all user-defined views in a database. Option 1 – The VIEWS Information Schema View 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. This article presents four ways to return a list of user-defined views in a SQL Server database.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |