Master_Views: master_objects, master_tables, etc

Leave a comment

May 23, 2016 by Kenneth Fisher

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,674 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: