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