SQL Server – Creating a View that Dynamically UNIONs New Tables

Someone added tables to the model database.

When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database. The rest of the new database is then filled with empty pages.

If you modify the model database, all databases created afterward will inherit those changes. For example, you could set permissions or database options, or add objects such as tables, functions, or stored procedures.

This will require really having fun with escaping and re-escaping single quotes, but I think the technique you're after is:

DECLARE 
  @DB  NVARCHAR(255) = QUOTENAME(N'dbname'),
  @SQL NVARCHAR(MAX);

SET @SQL =  N'EXEC ' + @DB + '.sys.sp_executesql '
+ 'N''CREATE VIEW dbo.vWhatever
AS
  SELECT x = 1, y = ''''x'''', z = GETDATE();''';

EXEC sys.sp_executesql @SQL;

So you're kind of nesting the dynamic SQL; the inner one ensures that the SQL is executed at the target database, not locally.

Another way that I've picked up since this original question appeared:

DECLARE 
  @DB  NVARCHAR(255) = QUOTENAME('dbname'),
  @SQL NVARCHAR(MAX),
  @DBExec NVARCHAR(MAX);

SET @DBExec = @DB + N'.sys.sp_executesql';

SET @SQL = N'CREATE VIEW dbo.whatever
  AS
    SELECT x = 1, y = ''x'', z = GETDATE();';

EXEC @DBExec @SQL;

This is slightly tidier because you don't have to double-nest single quotes.

And here is a slightly tidier way to do it without cursors (well, without all the scaffolding of setting up a cursor). Note that the inner dynamic SQL will only be executed in databases where (a) a categories table exists (b) this view does not already exist and (c) it is not a system database (well, not one of the primary system databases, anyway).

DECLARE @SQL NVARCHAR(MAX) = N'';

SELECT @SQL += NCHAR(13) + NCHAR(10)    
  + N'IF NOT EXISTS (SELECT 1 FROM ' + QUOTENAME(name) 
  + '.sys.views WHERE name = ''vNonhiddenCategories'')
  AND EXISTS (SELECT 1 FROM ' + QUOTENAME(name)
  + '.sys.tables WHERE name = ''categories'')
  BEGIN
    EXEC ' + QUOTENAME(name) + '.sys.sp_executesql N''
    CREATE VIEW dbo.vNonhiddenCategories3
    AS
      SELECT x = 1, y = ''''x'''';''
  END'
  FROM sys.databases 
  WHERE database_id BETWEEN 5 AND 32766;

PRINT @sql;
-- EXEC sp_executesql @sql;

Of course "tidier" is in the eye of the beholder.