I agree to TechTarget’s Terms of Use, Privacy Policy, and the transfer of my information to the United States for processing to provide me with relevant information as described in our Privacy Policy.

Please check the box if you want to proceed.

I agree to my information being processed by TechTarget and its Partners to contact me via phone, email, or other means regarding information relevant to my professional interests. I may unsubscribe at any time.

Please check the box if you want to proceed.

By submitting my Email address I confirm that I have read and accepted the Terms of Use and Declaration of Consent.

to make the process more easily repeatable. In fact, at least some Microsoft server products use this very technique behind the scenes as part of their setup process.

There are several reasons why you might want to incorporate a database schema into a SQL Server create database script. In addition to streamlining the development of multiple databases based on the same schema, you can use a script as a backup for re-creating databases in the event of a catastrophic system failure, or to set up development and test environments, or to train database administrators and developers on working with SQL Server.

Using SQL, you can generate a single script for a full database with all of its objects -- tables, indexes, stored procedures, etc. Alternatively, separate script files can be built for a single database object or a group of objects. Below you'll find basic examples of how to write and execute scripts for creating a database and a table within the database.

Create a create database script

It's really simple to write a SQL Server create database script without even having to use Microsoft's PowerShell scripting language and engine. You can see a sample script for creating a SQL Server database, written in Notepad, in Figure 1 below.

The first of the three lines of code checks to see if the database that we are creating currently exists. In this case, the script checks for the existence of a database named Demo beneath the master database. The second line of code creates the new database -- and, as you can see, it couldn't be simpler.

The final line of code tells SQL that this is the end of the block of instructions, and that it should execute the script and create the database. As you look at the image, you'll notice that the script's file name uses a .SQL extension. This is necessary so that SQL will recognize the script.

Figure 1: This script creates a database named Demo.

Use a script to create a database

To use the SQL Server create database script, you must open SQL Server Management Studio (SSMS). For the purposes of this article, I'm using SQL Server 2016, which doesn't include SSMS in its main installation routine. As a result, you need to download SSMS separately from Microsoft's website.

Once SSMS is open, click on the toolbar's Open File icon, and then select your script. You'll see the script code displayed within the SSMS window, as shown in Figure 2.

Figure 2: The create database script is displayed inside of SSMS.

In addition, a new row of icons is added to the toolbar beneath the Open File icon. Make sure the drop-down menu that now exists on the toolbar is set to Master, as shown in Figure 2. This indicates that the create database script will be run against the master database. Now, click the Execute button to execute the script.

When the execution completes, you should see a message like the one at the bottom of Figure 3, indicating that the script has executed successfully. You should also see the new database listed within the console tree. Remember, our script was designed to create a database named Demo, and you can see the newly created Demo database in the image.

Figure 3: The Demo database has been created.

Create a database table via a script

Now, let's take a look at how to script the creation of a table within the database that we just created. Some sample code that you can use is shown in Figure 4 below.

The code starts by checking for the existence of a table named MyTable. If the table doesn't already exist, it is created. The new table will contain three columns: a primary key named Id, and separate name and value columns. Notice that the column definitions are separated by commas in the script.

Figure 4: This script creates a database table named MyTable.

We can execute the script by using the same techniques that we did for the create database script, but with one key difference. Instead of selecting Master from the drop-down menu, we need to select Demo so that the table is created within the Demo database. You can see what this looks like in Figure 5.

Figure 5: This script is being run against the Demo database.

When the script is executed, the new table is created. You'll have to refresh the database by right-clicking on it and choosing the Refresh command before the new table becomes visible. Figure 6 shows the new table and its columns.

Figure 6: The script has created a new database table with three columns.

In all honesty, it probably would have been faster to create the database and its table manually. However, by using a SQL Server create database script and create table one, we've made it possible to add new versions of the database and table on an as-needed basis, and in a consistent manner.

Join the conversation

1 comment

Register

I agree to TechTarget’s Terms of Use, Privacy Policy, and the transfer of my information to the United States for processing to provide me with relevant information as described in our Privacy Policy.

Please check the box if you want to proceed.

I agree to my information being processed by TechTarget and its Partners to contact me via phone, email, or other means regarding information relevant to my professional interests. I may unsubscribe at any time.