Return to Revisions

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) = 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 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.

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.