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
Best Answer
This will require really having fun with escaping and re-escaping single quotes, but I think the technique you're after is:
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:
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).
Of course "tidier" is in the eye of the beholder.