How To Create Schema In Sql Server 2012

Create Schema and Table on other SQL Server Database

SQL developers can create database schema and table by using Transact-SQL scripts on SQL Server databases other than current database. In this SQL tutorial, I will not show how to create database schema but how to create a database schema on an other database automatically by running a SQL script easily.

Create Schema in SQL Server Database

To create a database schema in SQL Server CREATE SCHEMA command is used. After the command is executed, the named schema is created on the current SQL database.

CREATE SCHEMA newSchema

Code

SQL programmers can check the new schema under the Security > Schemas node if connected to the database using a SQL Server Management Studio as follows

SQL
List of schema objects created in a SQL Server Database

Create Schema in other SQL Databases

If the SQL Server developer or database administrator wants to create the schema on an other database using SQL commands automatically, he or she can build a dynamic SQL and execute it using sp_executesql system stored procedure. This is the first method.

An other method can be executing sp_MSForEachDB undocumented stored procedure and a corresponding dynamic SQL script.

On the other hand, if you have already tried to switch to a database in your dynamic SQL using "USE databasename" command and tried to execute "Create Schema" statement, you have probably got the following error.

declare @sql nvarchar(max) set @sql = N' USE myKodyaz GO CREATE SCHEMA myNewSchema' exec dbo.sp_executesql @sql

Code

The output error after we execute above SQL sp_executesql command:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'GO'.
Msg 111, Level 15, State 1, Line 4
'CREATE SCHEMA' must be the first statement in a query batch.

As I mentioned before, programmers and admins can choose one of the two simple ways to overcome this error and build dynamic SQL statements to create database schemas on different SQL Server databases.

Execute sp_executesql to Create Database Schema

For database administrators and SQL developers it is possible to execute sp_executesql system stored procedure on a different database.
Following SQL script simply runs the Create Schema command on the target database by using the sp_executesql sp which is on the target database using its fully qualified name.

declare @sql nvarchar(max) set @sql = 'CREATE SCHEMA myNewSchema' exec [kodyaz.test].dbo.sp_executesql @sql

Code

create database schema using sp_executesql on SQL Server
SQL Server

Execute sp_executesql to Create Database Schema

Another method is executing the sp_msforeachdb as seen in the following SQL code.
Although sp_msforeachdb stored procedure runs the @sql dynamic SQL script on every database on the current SQL Server instance, the IF clause which is checking for the target database enables us to target a single database for schema creation task.

create database schema using sp_msforeachdb on SQL Server
SQL Server

declare @database sysname = 'kodyaz.test'; declare @schema sysname = 'mynewschema'; declare @sql nvarchar(max) set @sql = N' if ''[?]'' = ''[' + @database + ']'' begin use [?]; -- create schema exec sp_executesql N''create schema ' + @schema + ''' end' exec sp_msforeachdb @sql

Code