Create view in a different database with dynamic SQL
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.