Giving error while creating schema

Question

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

abmore Hall of Fame Points: 3628 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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 · Answer 4

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 · Answer 5

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