Create multiple views inside a schema - SQL Server

Question

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?

If you want to put View1 into MySchema - just prefix the view name with the schema upon creation: CREATE VIEW MySchema.View1 AS ... — marc_s
– marc_s, Commented Dec 9, 2012 at 13:35
@marc_s, yes I know that, but then they wont act as a single unit. Doing my way will make sure either everything is created or nothing is. Which is important to me in this case. — BuddhiP
– BuddhiP, Commented Dec 9, 2012 at 13:43
@marc_s, taken from MSDN "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.", that's what I was looking for. — BuddhiP
– BuddhiP, Commented Dec 9, 2012 at 13:49

marc_s · Accepted Answer · 2012-12-09 13:31:06Z

6

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

edited Dec 9, 2012 at 13:31

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Dec 9, 2012 at 13:11

kgu87

2,05714 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Taryn Over a year ago

I am not sure why you got a downvote, this works. I just tested this on sql server 2008 r2

marc_s Over a year ago

@bluefeet: surprisingly - yes, this does seem to work just fine, and it does seem to create those views in the new schema. You never stop learning :-)

kgu87 Over a year ago

Thanks guys :) I did test it before posting.

Taryn Over a year ago

@marc_s I didn't think it would work either, that is why I tested it.

Martin Smith Over a year ago

Didn't know that either. The CREATE VIEW is considered part of the CREATE SCHEMA statement 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. This statement must be executed as a separate batch. Objects created by the CREATE SCHEMA statement are created inside the schema that is being created.

|

Collectives™ on Stack Overflow

Create multiple views inside a schema - SQL Server

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related