Giving error while creating schema

• abmore

  Hall of Fame

  Points: 3628

  More actions

  July 16, 2008 at 1:29 am

  #843567

  try it by using below script..

  USE [TEMP]

  GO

  IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = N'lsrSchema')

  BEGIN

  PRINT 'test Print'

  CREATE SCHEMA [lsrSchema] AUTHORIZATION [lsr]

  END

  GO

• Ian Scarlett

  SSC-Insane

  Points: 23197

  More actions

  July 16, 2008 at 2:15 am

  #843590

  Check BOL... it tells you that CREATE SCHEMA must be run in its own batch.

  Therefore you aren't going to be able to put any other statements (e.g. if exists...) in the same batch.

• calltoaneesh

  Old Hand

  Points: 319

  More actions

  July 28, 2008 at 12:22 am

  #848963

  Hi,

  I am using the following script

  set @Query='

  USE ['+@DatabaseName+']

  IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'''+@SchemaName+''')

  CREATE SCHEMA ['+@SchemaName+'] AUTHORIZATION ['+@UserName+']

  '

  exec (@Query)

  While executing its giving error "Incorrect syntax near the keyword 'SCHEMA". ..Please help me out to resolve this...

  Thanks,

  Aneesh

• soujanyaj

  SSC Enthusiast

  Points: 113

  More actions

  August 1, 2008 at 1:26 am

  #851676

  Hi,

  'create schema' must the be the first line in a batch. here is the

  workaround.

  USE [TEMP]

  IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'lsrSchema')

  begin

  select 'Test print'

  exec('CREATE SCHEMA [lsrSchema] AUTHORIZATION [lsr]')

  end

  try this

• subapriya

  SSC Enthusiast

  Points: 127

  More actions

  August 5, 2008 at 2:44 pm

  #853502

  USE [SampleDB]

  IF ((SELECT COUNT(*) FROM sys.schemas WHERE name = N'sampleSchema') = 0)

  BEGIN

  exec('CREATE SCHEMA [sampleSchema] AUTHORIZATION [dbo]')

  PRINT 'Schema Exists'

  END

  GO

  - Serves our purpose