There is a highly useful system view called sys.master_files. If you’ve never encountered it before it returns a list of all files from all databases. It’s been somewhat frustrating to me that there is no similar master_objects, master_tables, master_indexes etc. So what does a dev/dba do? Create them! So here is the code for a stored procedure that will create a master view. I should note that the way it stands it does not include the system databases but that’s an easy change. Just remove the commented line from the WHERE clause against sys.databases. Now unlike the system view these views would have to be refreshed every time a new database was created. Not really a big deal. Just put the stored procedure code into step one of a job, and the stored procedure calls to create your views in step two. Schedule the job to run daily before any other jobs using the views. Why daily? I certainly hope you aren’t creating new databases every day! It’s simply so you can forget it. Every database will be covered at least 24 hours after it’s created.

/*
-- Creates dbo.master_indexes by deafult
EXEC dbo.usp_make_master_view ;
-- Create dbo.master_objects
EXEC dbo.usp_make_master_view @master_view = 'master_objects', @sysview_name = 'objects';
-- Create dbo.master_tables
EXEC dbo.usp_make_master_view @master_view = 'master_tables', @sysview_name = '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 is not a valid schema.',
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 is not a valid system view.',
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

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL Tagged: microsoft sql server, system views, T-SQL