MS SQL Server 3 - Do this before creating table or adding column to the existing table. check if a table/column exists in a SQL Server table

Hello All,

Welcome to SQL Server article, My Self Chakrapani Upadhyaya a full stack engineer.

In this article we will find a way to check if a table/column exists in a SQL Server table.

Lets begin...

1. Lets Create the  new database EmpManagerDB

create database EmpManagerDB

2. use/open created database EmpManagerDB

use EmpManagerDB

3. create table called employees

create table employees
(
	empid				int primary key,
	fullname			nvarchar(200),
	emailId				nvarchar(200),
	phone				varchar(100),
	training_required	        varchar(3),
	status				varchar(10)
)

Check If Table Exists or Not

Syntax:

IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'TheSchema' 
                 AND  TABLE_NAME = 'TheTable'))
BEGIN
    --Do Stuff like print, drop table anything...
END

Example:

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES
		  WHERE TABLE_SCHEMA='dbo'
			AND TABLE_NAME='employee')
BEGIN
	Print 'Table employee exists in the database'
END
ELSE
BEGIN
	Print 'Table employee does not exists in the database'
END

And Result is

It will help us to drop and create the table if already exists. 

Check If Column Exists in table or Not

 Syntax:

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN
    --Do Stuff like print, drop table anything...
END

Example:

IF COL_LENGTH('dbo.employee', 'fullname') IS NOT NULL
BEGIN
   Print 'column fullname exists in the database'
END
ELSE
BEGIN
	Print 'column fullname does not exists in the database'
END

And Result is...

I hope you enjoyed this article, That's it for this article, See you in next article until then take care bye see you.