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
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
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.
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