Creating Views in Another Database

Have you ever had one of those times where you spent hours trying different ways to make something work the way you envisioned, only to find that the solution was staring you in the face all along? I have those more often than I'd like to admit – and while the title of this post seems like a DBA 101 topic, the answer isn't really all that simple. To hopefully help others (and perhaps my future self) I'll document a recent one here. I'm sure there will be plenty more in the future as well!

The story goes that I was working on a stored procedure to generate views in other databases via dynamic sql, and the hangup was that you can't create a view in another database – at least not easily. I realize that views can reference tables in other databases with no problem at all, but due to security restrictions and other issues outside the scope of this post, these views needed to exist in specific databases.

Creating a table in another database is a piece of cake because you can specify the database name in the CREATE TABLE statement:

1CREATE TABLE databaseName.schemaName.tableName (...

Creating a view, however, is not so easy. You can't specify a database name in the CREATE VIEW statement – doing so will get you a hot date with error 166. The only remark in Books Online is that "A view can be created only in the current database."

Since these views were being generated with dynamic sql, my next thought was to issue a USE [OtherDatabase] statement before the CREATE VIEW and just pass all of that into sp_executesql, the idea being that USE will change the context of execution to the desired destination database and all will be well. Unfortunately things didn't go according to plan:

This yields 3 errors – two for "Incorrect syntax near 'GO'", and another that "CREATE VIEW must be the first statement in a query batch." If GO signals the end of a batch, then removing the GOs makes it all one batch, right? Not quite. Losing the GOs will still give you the error 111, because CREATE VIEW must come first.

So how can we do this? After much experimentation I figured out that you can specify the database sp_executesql runs on by prepending a database and schema name to it just like other objects – something so simple that I thought right past it. It's kind of crazy to think that in all my years working with SQL Server I've never had a need to do it with dynamic sql until now, but that's the case.

If you weren't already aware of this, now you are. Hopefully it helps someone!