Can I create a VIEW that has a dynamic name in another DB with MS SQL?

Question

I found strange rules in MS SQL CREATE VIEW syntax. It must be on the first line of query batch processing and it must be created in the current database.

I should make VIEWs that have dynamic name described by string variables (type: VARCHAR or NVARCHAR). And those VIEWs should be created in other databases.

Because of the rule, CREATE VIEW statement must be on the first line of query batch processing, it cannot be after USE statement. So, I tried to change databases with USE & GO statement. But GO statement seemed to make clear all the variables. Therefore they are not available that describe VIEW name after GO statement.

Do you have any opinon for me? And if you know the reasons of CREATE VIEW syntax rules, please tell me.

---

Oh~, Sorry. I missed one thing. The names of databases are also dynamic. And VIEWs, I want to make, not only should access tables of other databases but also shoule be created in other databases.

Though I don't know OLAP well, I think this situation is involved OLAP.

Answer 1

You are able to dynamically create a sql-string and execute it.

 DECLARE @ViewName VARCHAR(100)

 SET @ViewName = 'MyView'

 USE MyDB;

 EXEC ( 'CREATE VIEW dbo.' + @ViewName + ' '
      + 'AS SELECT * FROM dbo.MyTable')

Answer 2

CREATE SYNONYM Resource1 FOR LinkedServer.Database.Schema.Table
GO
CREATE VIEW Resource1View
AS
SELECT *
FROM Resource1
GO

Now you can alter the synonym as much as you like and all your views referencing it will refer to the correct thing. If this doesn't solve the problem, then I would suggest that the way you're designing your system is not best. Please describe more what you are doing and why so we can advise you better.

As for "GO", is it actually not a SQL statement. It is never submitted to the server. The client sees the line with GO on it, and separates the submitted query into separate batches. A trace will prove this, as will EXEC 'SELECT 1' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 'SELECT 2'.

If you're using OLAP as in Analysis Services, then I'm not experienced enough with that to help you, but I would think there'd be ways to choose the database to connect to just like in SSRS, and that queries don't have to live in the database but could live in the SSAS application.

Answer 3

I found it. It's the nested EXEC.