Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
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. –  Lumpy Jun 15 '12 at 19:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.