How to create a database using SQL script feature in the SQL database modeling tool

Applies to

ApexSQL Model

Summary

In this article will be explained how to create a database using SQL script feature in the SQL database modeling tool

Description

ApexSQL Model provides the possibility to create an SQL script that can easily be used in other tools, for example, in the SQL Server Management Studio (SSMS), or can be saved locally as a backup or shared with other members in the team. This free SQL database modeling tool also provides a possibility to create the SQL script for one or more database tables.

This article will explain how to use the SQL script to create the database.

An SQL script can be created for a newly created table in ApexSQL Model or for a table from an already existing database that has been previously loaded.


More about how to create a table in ApexSQL Model can be found in the How to create or edit table using SQL data modeling tool article.

Creating an SQL script

When the tables are created in ApexSQL Model, the first step in the creation of SQL script process is to click on the Create script button under the Actions tab:

The Create script button

This action will open the Create script window where all created tables will be listed. Check tables that will be part of the SQL script and click the OK button:

The Create script window

The Editor window with SQL script will open. Here the SQL script can be saved, so it can be sent to another team member or executed in SSMS:

The created script using the SQL database modelling tool

Creating a database from a SQL script

When the SQL script is generated, the first statement will be for the creation of a database, which by default is the DefaultDb database. In the Editor window the database name can be changed before the SQL script is executed or saved:

The SQL script

To create the database from the generated SQL script, click on the Connect button in the Editor window. In the Connect to SQL Server window, select the desired SQL Server and type of authentication, and click on the Connect button:

The connection to the desired SQL server

Note: When creating a new database using the SQL script, the default option in the Database filed should be chosen.

To create a new database from the generated SQL script using the SQL database modeling tool is to click the Execute button:

List of the options in the SQL script window

If everything went as it should, the information message will appear confirming that the SQL script is executed successfully and the database is created:

Create database using SQL script

The created database will be listed in the Object Explorer panel into SQL Server Management Studio (SSMS):

Created database using SQL script which is created in the SQL database modelling tool

FAQs:

Q: Can I open the saved SQL script in any other tool besides ApexSQL Model?

A: Yes, the saved SQL script can be opened and reviewed in any tool that supports .sql files, like Notepad, Notepad++, SSMS, Visual Studio, etc.

Q: How can I create a SQL script for only the desired tables, not for all created tables in ApexSQL Model?

A: To create a SQL script only for the desired tables, in the Create script window, check only them and click the OK button. This way, only the checked tables will be integrated into a SQL script.

Select desired tables for creating SQL script

Q: Can I set the tables execution order in the SQL script?

A: Yes, this can be done in the Create script window. Select the already checked table and click on the appropriate buttons to move it down or up in the execution order:

Selecting order for the tables in the Create script window

Q: Can I update the database using the SQL script feature?

A: No, the updating database using SQL script is not possible in the SQL database modeling tool

Q: Can I execute the created SQL script in SSMS and create a database whenever I want?

A: Yes, the saved SQL script can be used in SSMS for creating a new database with the defined tables:

Executed SQL script in the SSMS which is created in the ApexSQL Model