При работе с SQL Server есть очень полезное системное представление под названием sys.master_files, которое возвращает список всех файлов из всех баз данных. Однако, может быть разочаровывающим, что нет аналогичных представлений для других объектов, таких как таблицы или индексы. Но не беспокойтесь, как разработчик или администратор баз данных, вы можете создать свои собственные мастер-представления!

Давайте начнем с создания хранимой процедуры, которая будет создавать мастер-представление. По умолчанию, хранимая процедура будет создавать представление для master_indexes. Однако, вы также можете создавать представления для других объектов, таких как master_objects или master_tables, указав желаемое имя объекта.

IF (OBJECT_ID('dbo.usp_make_master_view') IS NULL) 
    EXEC sp_executesql N'CREATE PROCEDURE dbo.usp_make_master_view 
AS SELECT * FROM sys.databases;';
GO

ALTER PROCEDURE dbo.usp_make_master_view (
@master_view nvarchar(500) = 'master_indexes'
,@master_view_schema nvarchar(500) = 'dbo'
,@sysview_schema nvarchar(500) = 'sys'
,@sysview_name nvarchar(500) = 'indexes')
AS
DECLARE @sql nvarchar(max) = '';
DECLARE @sql_columns nvarchar(max) = '';
DECLARE @sysview_object_id int;
DECLARE @server_collation nvarchar(500);
IF SCHEMA_ID(@master_view_schema) IS NULL
BEGIN
RAISERROR (N'%s не является допустимой схемой.',
16, 
1,
@master_view_schema);
RETURN
END

SELECT @sysview_object_id = ISNULL(object_id,0)
FROM sys.all_views 
WHERE name = @sysview_name 
  AND OBJECT_SCHEMA_NAME(object_id) = @sysview_schema
  AND object_id < 0;
IF @sysview_object_id IS NULL
BEGIN
RAISERROR (N'%s.%s не является допустимым системным представлением.',
16, 
1,
@sysview_schema,
@sysview_name);
RETURN
END
SET @sql = 'IF (OBJECT_ID('''+@master_view_schema+'.'+@master_view + ''') IS NULL) ' + char(10) + 
'    EXEC sp_executesql N''CREATE VIEW ' + quotename(@master_view_schema) + '.' 
+ quotename(@master_view) + char(10) + 
'AS SELECT * FROM sys.databases;''';
EXEC sp_executesql @sql;
SELECT @server_collation = CAST(SERVERPROPERTY('collation') AS nvarchar(500));
SET @sql_columns =  
STUFF(
(SELECT char(10) + ', ' + quotename(name) + 
CASE WHEN system_type_id IN (35, 99, 167, 175, 231, 239)
THEN ' collate ' + @server_collation + ' AS ' + quotename(name)
ELSE '' END
FROM sys.all_columns 
WHERE object_id = @sysview_object_id
FOR XML PATH('')
),1,1,'');
SET @sql = 
STUFF(
(SELECT char(10) + 'UNION ALL ' + char(10) + 
'SELECT ''' + name + ''' AS db_name ' + char(10) + 
@sql_columns + char(10) + ' FROM ' + quotename(name) + 
'.'+quotename(@sysview_schema)+'.'+quotename(@sysview_name) 
FROM sys.databases
-- Comment out this line to include system databases
WHERE database_id > 4
FOR XML PATH('')
),1,12,'');
SET @sql = 'ALTER VIEW ' + quotename(@master_view_schema) + '.' + quotename(@master_view) + char(10) + 
'AS ' + char(10) + 
@sql + ';';
--PRINT @sql
EXEC sp_executesql @sql

После создания хранимой процедуры, вы можете запланировать ее ежедневное выполнение в качестве задания. Хранимая процедура будет создавать мастер-представления для всех баз данных, исключая системные базы данных по умолчанию. Если вы хотите включить системные базы данных, просто удалите закомментированную строку из предложения WHERE против sys.databases.

Запуская задание ежедневно, вы гарантируете, что каждая база данных будет охвачена не позднее, чем через 24 часа после ее создания. Таким образом, вам не нужно беспокоиться о ручном обновлении представлений каждый раз, когда создается новая база данных.

С помощью мастер-представлений вы можете легко запрашивать информацию о объектах, таких как индексы, таблицы, или даже создавать свои собственные мастер-представления для других объектов. Это может быть особенно полезно для задач, таких как мониторинг или аудит объектов баз данных в нескольких базах данных.

Итак, воспользуйтесь гибкостью и мощью SQL Server, создавая свои собственные мастер-представления!