-- retrieving the data management views in use with the number of records they held DECLARE @view_name nvarchar(150) DECLARE @sql nvarchar(250) DECLARE @number_records bigint DECLARE @number_views int, @iterator int DROP TABLE IF EXISTS dbo.#views; CREATE TABLE dbo.#views ( ranking int NOT NULL , view_name nvarchar(150) NOT NULL ) INSERT INTO #views SELECT row_number() OVER(ORDER BY object_id) ranking , concat(schema_name(schema_id),'.', name) view_name FROM sys.all_views obj WHERE obj.Type = 'V' AND obj.is_ms_shipped = 1 --AND obj.name LIKE 'dm_exec_requests%'ORDER BY view_nameSET @iterator = 1 SET @number_views = IsNull((SELECT count(*) FROM #views), 0) WHILE (@iterator <= @number_views) BEGIN SET @view_name = (SELECT view_name FROM #views WHERE ranking = @iterator) SET @sql = CONCAT(N'SELECT @NumberRecords = count(*) FROM ', @view_name) BEGIN TRY --get the number of records EXEC sp_executesql @Query = @sql , @params = N'@NumberRecords bigint OUTPUT' , @NumberRecords = @number_records OUTPUT IF IsNull(@number_records, 0)> 0 BEGIN SELECT @view_name, @number_records END END TRY BEGIN CATCH -- no action needed in case of error END CATCH; SET @iterator = @iterator + 1 END DROP TABLE IF EXISTS dbo.#views;
Msg 15871, Level 16, State 9, Line 187
DMV (Dynamic Management View) 'dm_resource_governor_resource_pool_volumes' is not supported.
DMV (Dynamic Management View) 'dm_resource_governor_resource_pool_volumes' is not supported.
On the instance I tested the code, from a total of 729 DMVs only 171 records were returned, though maybe there are some views not shown because the feature related to them was not yet configured:
View name | Description |
INFORMATION_SCHEMA.COLUMNS | Returns one row for each column (*) |
INFORMATION_SCHEMA.PARAMETERS | Returns one row for each parameter of a user-defined function or stored procedure (*) |
INFORMATION_SCHEMA.ROUTINE_COLUMNS | Returns one row for each column returned by the table-valued functions (*) |
INFORMATION_SCHEMA.ROUTINES | Returns one row for each stored procedure and function (*) |
INFORMATION_SCHEMA.SCHEMATA | Returns one row for each schema in the current database |
INFORMATION_SCHEMA.TABLES | Returns one row for each table or view in the current database (*) |
INFORMATION_SCHEMA.VIEW_COLUMN_USAGE | Returns one row for each column in the current database that is used in a view definition |
INFORMATION_SCHEMA.VIEW_TABLE_USAGE | Returns one row for each table in the current database that is used in a view |
INFORMATION_SCHEMA.VIEWS | Returns one row for each view that can be accessed by the current user in the current database |
sys.all_columns | |
sys.all_objects | |
sys.all_parameters | |
sys.all_sql_modules | |
sys.all_views | |
sys.allocation_units | |
sys.assemblies | |
sys.assembly_files | |
sys.assembly_types | |
sys.columns | |
sys.configurations | |
sys.credentials | |
sys.data_spaces | |
sys.database_automatic_tuning_options | |
sys.database_automatic_tuning_options_internal | |
sys.database_credentials | |
sys.database_files | |
sys.database_filestream_options | |
sys.database_mirroring | |
sys.database_mirroring_endpoints | |
sys.database_permissions | |
sys.database_principals | |
sys.database_query_store_internal_state | |
sys.database_query_store_options | |
sys.database_recovery_status | |
sys.database_resource_governor_workload_groups | |
sys.database_role_members | |
sys.database_scoped_configurations | |
sys.database_scoped_credentials | |
sys.databases | |
sys.dm_exec_connections | |
sys.dm_exec_query_stats | |
sys.dm_exec_requests | Returns information about each request that is executing in SQL Server. |
sys.dm_exec_requests_history | Returns information about each request that executed in SQL Server; provided by Microsoft for troubleshooting. |
sys.dm_exec_sessions | |
sys.dm_external_data_processed | |
sys.dm_os_host_info | |
sys.dm_request_phases | Returns information about each request phase performed in request's execution. |
sys.dm_request_phases_exec_task_stats | Returns information about each task performed in request's execution. |
sys.dm_request_phases_task_group_stats | Returns information aggregated at task group level about each task performed in request's execution. |
sys.endpoints | |
sys.event_notification_event_types | |
sys.extended_properties | |
sys.external_data_sources | |
sys.external_file_formats | |
sys.external_language_files | |
sys.external_languages | |
sys.external_table_columns | |
sys.external_tables | |
sys.filegroups | |
sys.fulltext_document_types | |
sys.fulltext_languages | |
sys.fulltext_system_stopwords | |
sys.identity_columns | |
sys.index_columns | |
sys.indexes | |
sys.internal_tables | |
sys.key_encryptions | |
sys.linked_logins | |
sys.login_token | |
sys.master_files | |
sys.messages | |
sys.objects | |
sys.parameters | |
sys.partitions | |
sys.procedures | |
sys.query_store_databases_health | |
sys.query_store_global_health | |
sys.resource_governor_configuration | |
sys.resource_governor_external_resource_pools | |
sys.resource_governor_resource_pools | |
sys.resource_governor_workload_groups | |
sys.routes | |
sys.schemas | |
sys.securable_classes | |
sys.server_audit_specification_details | |
sys.server_audit_specifications | |
sys.server_audits | |
sys.server_event_session_actions | |
sys.server_event_session_events | |
sys.server_event_session_fields | |
sys.server_event_session_targets | |
sys.server_event_sessions | |
sys.server_memory_optimized_hybrid_buffer_pool_configuration | |
sys.server_permissions | |
sys.server_principals | |
sys.server_role_members | |
sys.servers | |
sys.service_contract_message_usages | |
sys.service_contract_usages | |
sys.service_contracts | |
sys.service_message_types | |
sys.service_queue_usages | |
sys.service_queues | |
sys.services | |
sys.spatial_reference_systems | |
sys.sql_dependencies | |
sys.sql_expression_dependencies | |
sys.sql_logins | |
sys.sql_modules | |
sys.stats | |
sys.stats_columns | |
sys.symmetric_keys | |
sys.sysaltfiles | |
sys.syscacheobjects | |
sys.syscharsets | |
sys.syscolumns | |
sys.syscomments | |
sys.sysconfigures | |
sys.syscurconfigs | |
sys.sysdatabases | |
sys.sysdepends | |
sys.sysfilegroups | |
sys.sysfiles | |
sys.sysindexes | |
sys.sysindexkeys | |
sys.syslanguages | |
sys.syslockinfo | |
sys.syslogins | |
sys.sysmembers | |
sys.sysmessages | |
sys.sysobjects | |
sys.sysoledbusers | |
sys.sysperfinfo | |
sys.syspermissions | |
sys.sysprocesses | |
sys.sysprotects | |
sys.sysservers | |
sys.system_columns | |
sys.system_components_surface_area_configuration | |
sys.system_internals_allocation_units | |
sys.system_internals_partition_columns | |
sys.system_internals_partitions | |
sys.system_objects | |
sys.system_parameters | |
sys.system_sql_modules | |
sys.system_views | |
sys.systypes | |
sys.sysusers | |
sys.tables | |
sys.tcp_endpoints | |
sys.time_zone_info | |
sys.trace_categories | |
sys.trace_columns | |
sys.trace_event_bindings | |
sys.trace_events | |
sys.trace_subclass_values | |
sys.trigger_event_types | |
sys.type_assembly_usages | |
sys.types | |
sys.user_token | |
sys.via_endpoints | |
sys.views | |
sys.xml_schema_attributes | |
sys.xml_schema_collections | |
sys.xml_schema_component_placements | |
sys.xml_schema_components | |
sys.xml_schema_facets | |
sys.xml_schema_model_groups | |
sys.xml_schema_namespaces | |
sys.xml_schema_types | |
sys.xml_schema_wildcards |
Notes:
1) As can be seen, also the INFORMATION_SCHEMA views don't seem to be fully supprted.
2) "(*)" in description marks the views that can be accessed by the current user in the current database.
1) As can be seen, also the INFORMATION_SCHEMA views don't seem to be fully supprted.
2) "(*)" in description marks the views that can be accessed by the current user in the current database.
3) I removed the number of records as they are instance specific.
4) The code should work also on a dedicated SQL Server pool.
5) I hope to come back and showcase the usage of some of the most important views.
4) The code should work also on a dedicated SQL Server pool.
5) I hope to come back and showcase the usage of some of the most important views.
6) The script can be used for the Microsoft Fabric Warehouse, however each record will be shown in a different panel! One can use an additional temporary table to save the results or extend the views table and update the table with the result, like in the following script:
-- retrieving the data management views in use with the number of records they held DECLARE @view_name nvarchar(150) DECLARE @sql nvarchar(250) DECLARE @number_records bigint DECLARE @number_views int, @iterator int DROP TABLE IF EXISTS dbo.#views; CREATE TABLE dbo.#views ( ranking int NOT NULL , view_name nvarchar(150) NOT NULL , record_count bigint NULL ) INSERT INTO #views SELECT row_number() OVER(ORDER BY object_id) ranking , concat(schema_name(schema_id),'.', name) view_name , NULL record_count FROM sys.all_views obj WHERE obj.Type = 'V' AND obj.is_ms_shipped = 1 --AND obj.name LIKE 'dm_exec_requests%' ORDER BY view_name SET @iterator = 1 SET @number_views = IsNull((SELECT count(*) FROM #views), 0) WHILE (@iterator <= @number_views) BEGIN SET @view_name = (SELECT view_name FROM #views WHERE ranking = @iterator) SET @sql = CONCAT(N'SELECT @NumberRecords = count(*) FROM ', @view_name) BEGIN TRY --get the number of records EXEC sp_executesql @Query = @sql , @params = N'@NumberRecords bigint OUTPUT' , @NumberRecords = @number_records OUTPUT IF IsNull(@number_records, 0)>= 0 BEGIN UPDATE #views SET record_count = @number_records WHERE view_name = @view_name END END TRY BEGIN CATCH -- no action needed in case of error END CATCH; SET @iterator = @iterator + 1 END SELECT * FROM dbo.#views; DROP TABLE IF EXISTS dbo.#views;
Happy coding!
No comments:
Post a Comment