IMG-LOGO

How to create table in TSQL?

andy - 09 Dec, 2020 952 Views 0 Comment

Learn how you can easily create a table using a TSQL query. We are going to perform a quick check if a table that we are going to create does not exist in the database. This is to ensure we do not get any errors.

In this example, we are going to create a user table that will have a primary key with an identity field which is known as auto number index.

See below the TSQL query code. We are using the if condition with keyword NOT EXISTS against the sysobjects table that will hold all the user creation table's names. 
If the condition returns a false value we then perform the creation of the user table. This is to ensure when you re-run the query again. It will not generate any error table creation message.


IF NOT EXISTS (SELECT * from dbo.sysobjects where id = object_id(N'Users') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
CREATE TABLE Users
(
    [UserID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [FirstName] nvarchar(30),
    [LastName] nvarchar(30),
    [Email] nvarchar(50),
    [Password] nvarchar(50),
    [CreatedOnDate] datetime DEFAULT GETUTCDATE(),
    [ModifiedOnDate] datetime DEFAULT NULL,
    [CreatedBy] [numeric](18, 0) DEFAULT(0),
    [UpdatedBy] [numeric](18, 0) DEFAULT(0)
)

ALTER TABLE [Users] ADD CONSTRAINT [PK_Users] PRIMARY KEY NONCLUSTERED  ([UserID])
END
GO

Comments

There are no comments available.

Write Comment
0 characters entered. Maximum characters allowed are 1000 characters.

Related Articles

How to restore database using SQL query in TSQL?

If you have a database backup bak file extension and want to restore it using SQL query You can use the built in RESTORE DATABASE function Remember in order to be able to restore a database successfully You need to ...

How to get all table sizes in TSQL?

To get the information about how much space or size used by tables You can retrieve the size information by linking multiple tables in sys tables There are two tables that hold this information The first one is the sys ...