Create view in a different database with dynamic SQL

Question

As part of a larger project I'm trying to create a new view on each database in a SQL Server instance.

I've created a stored procedure that among other things is looping through the databases in the instance via a cursor then trying to create the view using sp_executesql. Unfortunately the stored procedure does not exist in the databases that I am trying to create the views on. So I'm trying to get sql server to use that database and then run the create view script. When I run this I receive the error

'CREATE VIEW' must be the first statment in a query batch.

Any ideas on how I can accomplish this? Here is the sp_executesql portion of the procedure.

declare @DB VARCHAR(50)
declare @SQL NVARCHAR(max)
set @DB = '[dbname]'
SET @SQL = N'USE ' + @DB + N' CREATE VIEW vNonhiddenCategories 
AS
SELECT categories.categoryid
FROM categories
--WHERE ... irrelevant remainder of view code ...
'
execute sp_executesql @SQL

Answer 1

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.

• Lumpy: That's awesome. Don't know why it didnt occur to me to nest it like that but well done sir. Thank you for the help.

Answer 2

use for each database :

EXEC sp_msforeachdb 'USE [?] 
declare @SQL nvarchar(max) 
SET @SQL =''CREATE VIEW AS ...
BEGIN
EXEC [?].sys.sp_executesql @SQL 
END'

• LowlyDBA - John M: This undocumented stored proc is not reliable. sp_ineachdb is a much better alternative.