SQL Server 2008 R2: 'CREATE/ALTER VIEW' does not allow specifying the database name as a prefix to the object name

Question

I have to 2 databases DB1 and DB2.

I have a view called View1 which is stored in the database DB2. Now I want to ALTER VIEW from DB1 database.

My attempt:

ALTER VIEW DB2..View1 
AS 
   SELECT * FROM DB2..Test;

But I'm getting an error:

'CREATE/ALTER VIEW' does not allow specifying the database name as a prefix to the object name

Ghiselin

If the view is in DB2, you need to be in DB2 to alter it. – Renter

@marc_s, So its not possible from DB1? – Ghiselin

The error message seems to tell you that this is not possible.... – Renter

Are you working in DB2? – Kienan

As pointed out already, the error message is very clear you can't do this. Why do you need to alter a view when in the context of a different database? Just change the context of your current connection and alter the view. – Aureole

@SeanLange, Thank you so much. – Ghiselin

@marc_s, Thank you so much. – Ghiselin

Answer 1

It is really simple: you need to change the database

USE DB2
GO

ALTER VIEW View1
    ...

Succinct

Yup! Got it. Thank you so much. – Ghiselin

What would be the recommended way of doing this if the database is parametrized? – Oryx

Answer 2

In my case I'm trying to run a script to create/alter a view in different database, so I'm using EXEC (...) to create my views..
But I hit a bit of a paradox: EXEC ('CREATE VIEW...') will not let you specify the database. You have to switch to that database to create the view.
But you can't do EXEC ('USE [db]; CREATE VIEW...') as CREATE VIEW will demand it be the first command. I got around this problem feeling like I went Inception:
        EXEC('USE [db]; EXEC('CREATE VIEW'))

Montane

You are a genius! I was looking for a solution for this problem for far longer than I'd like to admit. – Fugato

Answer 3

Comments are self explanatory. You need to be working in the DataBase your view is for/from. Switch your connection to DB2 and you should be able to CREATE and ALTER a/your view. From MSDN

Kienan

Answer 4

I was connected to the same database server:DB1 in Azure Data Studio where I wanted the view, and getting the error from this:

CREATE OR ALTER VIEW DB1.dbo.my_View AS
SELECT ...

I removed the prefix and this worked:

CREATE OR ALTER VIEW my_View AS
SELECT ...

Elo

Answer 5

Eric's genius answer above saved my bacon. One thing to note is that the second encapsulated EXEC will need two single quotes to open/close it, ala: EXEC(''...'').

Also, in my case, I was creating a stored procedure, and I consequently needed to QUADRUPLE the single quotes in the stored procedure to make things work. So,

DECLARE @Name varchar(25) = ''

became

DECLARE @Name varchar(25) = ''''''''

Fortunately, search and replace made this easy.

Issacissachar

instead of all this, you can use declare @sp nvarchar(776) = 'yourdb.sys.sp_executesql' exec @sp N'create view yourview as ...' – Lexy

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review – Seger