CREATE SCHEMA in db A from a stored procedure in db B

Hi All

I have a SP that i create tables and other objects on another database.

Creating table work well.

declare @.s nvarchar(2000)
set @.s = 'use db01'
set @.s = @.s + 'CREATE TABLE ABC (recid int)
exec (@.s)

But if i try to create a schema it gives error :

'CREATE SCHEMA' must be the first statement in a query batch.

declare @.s nvarchar(2000)
set @.s = 'use db01'
set @.s = @.s + 'CREATE SCHEMA AAA
exec (@.s)

How can i solve it?

Thanks.

declare @.s nvarchar(2000)
set @.s = 'use db01'

exec (@.s)
set @.s = 'CREATE SCHEMA AAA
exec (@.s)

|||

Hi Asvin

If i use "use db01" in exec, when exec completes, it gives up using db01, so if i'm running script in db02, 'CREATE SCHEMA AAA' works on db02.

Best regards.

|||

Standard disclaimer. Generally not a good idea to be creating tables and schemas on the fly.

That out of the way, this method should work:

exec('use tempdb; exec sp_executesql N''create schema test''')

|||

You can do the following in SQL Server 2000/2005 (similar approach can be used in SQL70 also):

declare @.sp nvarchar(500)

set @.sp = quotename(N'db01') + N'sys.sp_executesql'

exec @.sp 'CREATE SCHEMA AAA....'

-- or

exec @.sp @.s