none
check if schema exists

Answers

  • 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.

     

    Tuesday, September 12, 2006 3:49 AM

All replies

  • 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.

    Monday, September 11, 2006 9:57 PM
  • 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.

     

    Tuesday, September 12, 2006 3:49 AM
  • Sorry. I should have mentioned that I am talking about schema in the database; not xml schema. Thanks for the help.
    Tuesday, September 12, 2006 11:45 AM
  • 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

    Tuesday, May 22, 2007 11:40 AM