Creating a Schema if it doesn't exist

Question

Post reply

Creating a Schema if it doesn't exist

RedDoorSQL

Ten Centuries

Points: 1294
More actions
March 14, 2012 at 8:36 am

#391795

I've create a small script which creates some tables but i'm have aproblem with creating a schema if it doesn't exist, basically we need these to setup process one multiple servers
can someone help as I can't see where the problem is
IF NOT EXISTS (SELECT SCHEMA_ID FROM sys.schemas WHERE [name] = 'TestSche')
BEGIN
CREATE SCHEMA TestSche AUTHORIZATION dbo
END
and is return error near create 🙂

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

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

Ant-Green SSC Guru Points: 113499 More actions · Answer 1

i had the same issue, had to take it out of the begin end due to it expecting external

so i ended up doing this

IF EXISTS (SELECT name FROM sys.schemas WHERE name = N'DB')

BEGIN

PRINT 'Dropping the DB schema'

DROP SCHEMA [DB]

END

GO

PRINT ' Creating the DB schema'

GO

CREATE SCHEMA [DB] AUTHORIZATION [dbo]

GO

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

CREATE SCHEMA must be in its own batch. What that means in context of your script if you will need to execute your create schema via dynamic sql.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

Sean Lange (3/14/2012)
CREATE SCHEMA must be in its own batch. What that means in context of your script if you will need to execute your create schema via dynamic sql.

+1

Here is what Sean meant (using Anthony's sample code):

IF NOT EXISTS ( SELECT *

FROM sys.schemas

WHERE name = N'DB' )

EXEC('CREATE SCHEMA [DB] AUTHORIZATION [dbo]');

GO

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

RedDoorSQL Ten Centuries Points: 1294 More actions · Answer 4

Thanks Guys,I had suspected it needed to be in its own batch after a little playing around.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

If you post your original sql into a ssms window the create schema will be red squiggly. On mouse over it will tell you exactly that. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/