To create a table in SQL Server using a query:

1. In the SQL Server Management Studio, click the New Query button on the toolbar
2. Type or paste a CREATE TABLE script (example below)
3. Click the ! Execute button on the toolbar

Here’s an example:

The CREATE TABLE Script

Here’s the SQL script in the above example:

CREATE TABLE Status(
StatusId int IDENTITY(1,1) NOT NULL,
StatusName varchar(50) NOT NULL,
DateCreated datetime NOT NULL CONSTRAINT DF_Status_DateCreated DEFAULT (getdate()),
CONSTRAINT PK_Status PRIMARY KEY CLUSTERED (StatusId)
)

The script does the following:

• creates a table
• adds 3 columns
• sets their data type
• specifies that the StatusId is an identity column and that the value of the first record will be 1, and that the value for each subsequent record will increment by 1
• specifies that any value in the StatusName column can have a maximum length of 50
• specifies that NULL values are not allowed
• sets a default value of the current date for the DateCreated column
• sets the StatusId field as the primary key

This is a basic script that only creates one small table. You could just as easily run a script that creates a whole database, with all its objects and data and permissions all in one go.

Improving the Script

When creating database objects, it’s good practice to check to see whether the object already exists or not. This prevents errors occurring when the script tries to create a table that is already in the database.

Only Run if the Table Doesn’t Already Exist

The following line could be added to the above script, so that, the CREATE TABLE script will only run if the table doesn’t already exist in the database (or a table with the same name):

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='Status' AND xtype='U')

So the whole script would become:

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='Status' AND xtype='U')
CREATE TABLE Status(
StatusId int IDENTITY(1,1) NOT NULL,
StatusName varchar(50) NOT NULL,
DateCreated datetime NOT NULL CONSTRAINT DF_Status_DateCreated DEFAULT (getdate()),
CONSTRAINT PK_Status PRIMARY KEY CLUSTERED (StatusId)
)

Replace the Table if it Already Exists

The script could also be rewritten to replace the table if it already exists. This can be done by first, dropping the existing table, then creating the new one.

SQL Server 2016

SQL Server 2016 introduced the DROP IF EXISTS clause. So in SQL Server 2016, we can add the following to the top of the script:

DROP TABLE IF EXISTS [TaskTracker].[Status]

(TaskTracker is the name of the database. Status is the name of the table).

DROP TABLE IF EXISTS [TaskTracker].[Status]
CREATE TABLE Status(
StatusId int IDENTITY(1,1) NOT NULL,
StatusName varchar(50) NOT NULL,
DateCreated datetime NOT NULL CONSTRAINT DF_Status_DateCreated DEFAULT (getdate()),
CONSTRAINT PK_Status PRIMARY KEY CLUSTERED (StatusId)
)

SQL Server 2014 and Earlier

If using an earlier version of SQL Server, you can use the following to drop the existing table:

IF EXISTS (SELECT * FROM sysobjects WHERE name='Status' AND xtype='U')
DROP TABLE Status

So the whole script would become:

IF EXISTS (SELECT * FROM sysobjects WHERE name='Status' AND xtype='U')
DROP TABLE Status
CREATE TABLE Status(
StatusId int IDENTITY(1,1) NOT NULL,
StatusName varchar(50) NOT NULL,
DateCreated datetime NOT NULL CONSTRAINT DF_Status_DateCreated DEFAULT (getdate()),
CONSTRAINT PK_Status PRIMARY KEY CLUSTERED (StatusId)
)

Generating a Script from an Existing Table

You can generate a SQL script from an existing table via the Object Explorer in the SSMS GUI. When you do this, SQL Server generates all the SQL code from the table and creates the script.

To do this, simply right-click on the table and select Script table as... then follow the prompts.

This is not just limited to tables – you can script any database object using the same method.

If you’re new to creating SQL scripts, you could try creating a table using the GUI, then generate the SQL script from the table.