Published on: 14 April, 2016

How to check if a table exists in SQL server?

Tumblr Digg Google Plus

Before running an SQL script to create a table dynamically, it would be a good step if you perform a checking if a table already exists. If it does, you may want to skip creating the table otherwise you will get an error exception.

To check if a table exists in the database, you can use the following sql inline script.

IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.[my_table_name]') and OBJECTPROPERTY(id, N'IsTable') = 1)
	BEGIN
		CREATE TABLE dbo.[my_table_name]
		(
			[ID] [int] IDENTITY(1,1) NOT NULL ,
	                [Name] NVARCHAR(200)
		)
	END
GO

If you want to drop an existing table, it is recommended that you perform a check as well. You can see the following example.

IF EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.[my_table_name]') and OBJECTPROPERTY(id, N'IsTable') = 1)
	BEGIN
		DROP TABLE dbo.[my_table_name]
	END
GO

Another great example if you want to add a column to an existing table.

IF EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.[my_table_name]') and OBJECTPROPERTY(id, N'IsTable') = 1)
	BEGIN
		ALTER TABLE dbo.[my_table_name]
                ADD Email NVARCHAR(50)
	END
GO

Comments

There are no comments available.

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