How to Test Existence of Database Object in Transact-SQL

In this article I am going to explain about how to test existence of database object in Transact-SQL.
  • 2068

Introduction

Transact-SQL (T-SQL) is Structured Query Language and it is Microsoft's and Sybase's proprietary. Transact-SQL is originally developed by IBM for querying, altering and defining relational database. Transact SQL is also known as T-SQL. T-SQL supports many feature like variables, loops, conditions  and exceptions.

Database objects

There are many types of database objects like view, store procedure and user define function.  To check the existence of these objects and database, SQL server provide some method  like OBJECT_ID and DB_ID. OBJECT_ID function is used to check the existence of table, view, store procedure and user define function.  DB_ID is used to check the existence of specific database.

Statement that check database existence

USE master

IF DB_ID('mydb') IS NOT NULL

DROP DATABASE mydb

Statement that create a table

create table copymcnemp

id int, 

name varchar(15), 

age int, 

city varchar(15), 

salary money 

)

Statement that check existence of object

IF OBJECT_ID('copymcnemp') IS NOT NULL
DROP TABLE dbo.copymcnemp


© 2019 DotNetHeaven. All rights reserved.