check if schema exists

How can I tell if a schema already exists so that it doesn't have to be created?

If your schema should exist in the form of an .xsd file then you can use the system.io.file object as:

if system.io.file(path\name.xsd).exists then

the exists method will return a true if the file is present.

If this is not what you are looking for, where else might a "schema" exist that you would have to create one? In SQL? There too is the "Exists" keyword in t-SQL, you can, for instance create an SP that would look something like:

[based on the pubs demo db]

CREATE PROCEDURE dbo.MakeEmployeeTable

AS

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[employee]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [dbo].[employee] (
[emp_id] [empid] NOT NULL ,
[fname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[minit] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lname] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[job_id] [smallint] NOT NULL ,
[job_lvl] [tinyint] NULL ,
[pub_id] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[hire_date] [datetime] NOT NULL
) ON [PRIMARY]

GO

This checks to see if a table is present and if it is not, adds it, with a given schema.

|||

I will take the other path and assume you meant relational schema :)

I use:

if schema_id('dog') is null
execute('create schema dog') --exec because 'CREATE SCHEMA' must be the first statement in a query batch.

|||Sorry. I should have mentioned that I am talking about schema in the database; not xml schema. Thanks for the help.|||

if exists(select * from sys.schemas where name = 'myschema')

begin

--ADD YOUR CODE HERE

end

else

begin

--ADD YOUR CODE HERE

--CREATE SCHEMA [myschema] AUTHORIZATION [dbo]

end