SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download 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

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

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

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






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems