Conditional CREATE SCHEMA

Hi,
I would like to write TO-SQL batches that call the CREATE SCHEMA...
statement. However, there is a reasonable likelihood that the schema I want
to create already exists. However, the CREATE SCHEMA statement has the
limitation that it be the first command in a batch. I found out the hard
way that this means that I can't run a batch like this...
IF (SCHEMA_ID('MySchema') IS NULL)
CREATE SCHEMA [MySchema] AUTHORIZATION dbo;
How do I conditionally create a schema based on whether it currently exists?
I came up with this, but I'd like to think there is something a little more
elegant.
DECLARE @.CS varchar(255);
SET @.CS = 'CREATE SCHEMA [MySchema] AUTHORIZATION dbo;';
IF (SCHEMA_ID('MySchema') IS NULL)
EXEC (@.CS);
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.orgHi Daniel
Although you may not think your solution elegant it is a solution to the
issue which can be useful, for instance if you want to take some action
outside the scope of the current transaction.
John
"Daniel Jameson" wrote:
> Hi,
> I would like to write TO-SQL batches that call the CREATE SCHEMA...
> statement. However, there is a reasonable likelihood that the schema I want
> to create already exists. However, the CREATE SCHEMA statement has the
> limitation that it be the first command in a batch. I found out the hard
> way that this means that I can't run a batch like this...
> IF (SCHEMA_ID('MySchema') IS NULL)
> CREATE SCHEMA [MySchema] AUTHORIZATION dbo;
> How do I conditionally create a schema based on whether it currently exists?
> I came up with this, but I'd like to think there is something a little more
> elegant.
> DECLARE @.CS varchar(255);
> SET @.CS = 'CREATE SCHEMA [MySchema] AUTHORIZATION dbo;';
> IF (SCHEMA_ID('MySchema') IS NULL)
> EXEC (@.CS);
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
>|||Hi Daniel,
I understand that you would like to find an elegant way to create a schema
if the schema does not exist.
If I have misunderstood, please let me know.
I think that your current workaround is elegant. If you use "Generate
Scripts... " to generate the script of schema objects in SQL Server 2005
Management Studio, you will find the following scripts:
USE [AdventureWorks]
GO
/****** Object: Schema [HumanResources] Script Date: 09/17/2007
13:41:51 ******/
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'HumanResources')
EXEC sys.sp_executesql N'CREATE SCHEMA [HumanResources] AUTHORIZATION [dbo]'
GO
As you can see, it is very similar to yours. Currently I do not think that
there is a better way due to the design limitation of using CREATE SCHEMA.
Please feel free to let us know if you have any other questions or
concerns. Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Daniel,
What is this issue going on?
If there is any issue, please feel free to post back. We are very glad for
further assistance.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================