SQL Server 2016: Create a Table from an SQL Script

In SQL Server, you can create tables by running an SQL script with the CREATE TABLE statement.

Having just created a table via the SSMS GUI, we will now create a table (or two) using an SQL script.

To create a table using SQL, use the CREATE TABLE statement. Provide the column names and their data types, plus any other properties as required.

  1. Open a New Query Window

    Screenshot of creating a table via SQL script.

    Open a new query window by clicking on New Query in the toolbar.

  2. Run the SQL Script

    Screenshot of creating a table via SQL script.

    Run the SQL script (below) by pasting it into the query window, then clicking the Execute button on the toolbar.

    You can also run a query by:

    • Pressing F5 on your keyboard.
    • Clicking Query > Execute from the top menu.
    • Right-clicking in the actual query window and selecting Execute from the contextual menu.

    The SQL Script

    Here's the code to copy and paste:

  3. Check that the Table was Created

    Screenshot of the query results.

    Let's also use SQL to check that the table was created.

    Run SELECT * FROM sys.tables; to return a list of all tables in the database.

    Of course, you can also refresh the Object Explorer by right-clicking on the Tables node and selecting Refresh like we did when we created our previous table.

Table Options

The CREATE TABLE statement accepts many additional options that allow you to specify the exact properties of the table.

There are too many to go into detail in this tutorial, but they include options for encryption, replication, indexes, and more.

However, we will be covering one of these options next — foreign key constraints — when we create a relationship between tables.