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