CODEWITHSUNDEEP
sql-servert-sqlviewsql-server-2008-r2
BuddhiP
BuddhiP

Reputation: 6451

Create multiple views inside a schema - SQL Server

How can I create multiple views inside the CREATE SCHEMA statement?

I want to create a SCHEMA, and create two views inside it in the same statement, so all those statements work as a one unit? Succeed or fail together!

From MSDN: http://msdn.microsoft.com/en-us/library/ms189462.aspx

"CREATE SCHEMA can create a schema, the tables and views it contains, and GRANT, REVOKE, or DENY permissions on any securable in a single statement. CREATE SCHEMA transactions are atomic. If any error occurs during the execution of a CREATE SCHEMA statement, none of the specified securables are created and no permissions are granted."

,

How can I do this? I tried this:

CREATE SCHEMA [MYSCHEMA] AUTHORIZATION [dbo]
    CREATE VIEW [VIEW1]
    AS 
        SELECT [ID]
               ,[NAME]
                FROM [dbo].[TABLE1]
        /* Here is the Problem */
        GO

        CREATE VIEW [VIEW2]
        AS
        SELECT [ID]
               ,[NAME]
                FROM [dbo].[TABLE2]
        GO

If I include a GO statement just after first view creation, then script runs but second view VIEW2 is created under the dbo schema, not under MYSCHEMA, and doesn't run as a single unit either.

If I remove the GO after the first view, then it gives an error saying

CREATE VIEW must be the first statement of a batch

for the second CREATE VIEW statement.

How do I solve this and create both views as a part of CREATE SCHEMA statement?

Upvotes: 3

Views: 10240

Answers (1)

kgu87
kgu87

Reputation: 2057

CREATE SCHEMA [MYSCHEMA] AUTHORIZATION [dbo]

CREATE VIEW [VIEW1] AS SELECT [ID], [NAME] FROM [dbo].[TABLE1]
CREATE VIEW [VIEW2] AS SELECT [ID], [NAME] FROM [dbo].[TABLE2]
GO

Upvotes: 6

Related Questions