How can I create a new database with schema within a stored procedure?

Question

I want to write a TSQL stored procedure that creates a database with a specified name, and pre-populates it with some schema.

So I use lots of EXEC statements:

EXEC('CREATE TABLE ' + @dbName + '.dbo.MyTable (...)');

etc, along with some CREATE PROCEDURE, CREATE FUNCTION etc. However, the problem comes from when I want to create a type, as CREATE TYPE statements can't have the database specified, and you can't have USE @dbName within the stored procedure.

How can I create a type in another database in a stored procedure?

Answer 1

There are certain commands that can't use used as ssarabando suggests, among them is CREATE SCHEMA, which throws Msg 111 when used in with that technique.

The work around is to nest dynamic SQL blocks as follows:

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

The outer block does nothing except change the database, so that the inner block has the correct context when it is executed.

Notice that the inner parameter to sp_executesql needs two single quotes.