IMG-LOGO

How to check if a table exists in SQL server?

andy - 14 Apr, 2016 29952 Views 0 Comment

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.

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 ...