Posted by Erland Sommarskog on 01/03/07 22:33

fireball (fireball@onet.kropka.eu) writes:
> Uzytkownik "Erland Sommarskog" <esquel@sommarskog.se> napisal w wiadomosci
>
> once in my script:
> declare @db varchar(255), @sch varchar(255)
> set @db = 'somebase'
> set @sh = 'someschema'
>
> But I failed trying sql:
> create schema somebase.someschema
> /set @sql = 'create schema ' + quotename(@db) + '.' + quotename(@sch);
> exec sp_executesql @sql/
> - how to name it fully?

The topic for CREATE SCHEMA starts off

Creates a schema in the current database.

Normally you can get away with a USE first in your dynamic SQL, but
CREATE SCHEMA must be alone in a batch. Well, you could do:

EXSC('USE ' + @db + ' EXEC(''CREATE SCHEMA ' + @sch + ''')')

(But use quotename() to deal with the mess of nested quotes.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: many tables vs one big table
• Prev in forum: Re: Problem with Updatetext in SQL 2000 trying to concatenate multiple text fields
• Thread view: Re: when to use fully qualified names ([database].[schema].object)

[Reply to this message]