Sql Server: Check if Exists

In my day job I am always writing sql DDL statements, such as, creating tables, indexes, keys, constraints, store procedures, etc. The problem I have is that I can’t remember which way to test for existence of each object type. Some, like tables and stored procedures, only require adding the IF [NOT] EXISTS to the CREATE statement. But indexes, keys, constraints, schemas, and database require doing a lookup inside of a IF NOT EXISTS ( ) BEGIN ‘create/drop’ END query structure. Here are some example that you can use as a quick reference. Lord knows I need it quite often.

Database existence

IF NOT EXISTS
(
    SELECT name FROM master.dbo.sysdatabases
    WHERE name = N’NameOfDatabase’
)
CREATE DATABASE [NameOfDatabase]
GO

If your user does not have access to master database try the following:

if db_id(‘NameOfDatabase’) is not null
BEGIN
SELECT ‘Database Exists’
END
ELSE
BEGIN
    SELECT ‘Database Does NOT Exist’
    – Create the database
    CREATE DATABASE [NameOfDatabase]
END

Schema existence

In the below, you can omit the AUTHORIZATION part to default to ‘dbo’

IF NOT EXISTS (
SELECT schema_name FROM information_schema.schemata
WHERE schema_name = ‘NameOfSchema’)
BEGIN
    EXEC sp_executesql N’CREATE SCHEMA [NameOfSchema] AUTHORIZATION [dbo]’
END
GO

Create an index if it doesn’t exist

IF NOT EXISTS (
SELECT 1
    FROM sys.indexes I
    INNER JOIN sys.tables T ON I.object_id = T.object_id
    INNER JOIN sys.schemas S ON S.schema_id = T.schema_id
    WHERE I.Name = ‘IX_Name_Of_Index’ – Index name
       AND T.Name = ‘NameOfTable’ – Table name
       AND S.Name = ‘NameOfSchema’ –Schema Name
)
BEGIN
    SELECT ‘Index NOT Exists’
    – Create the index
END

OR

IF NOT EXISTS (
    SELECT 1 FROM sys.indexes I

    WHERE I.Name = ‘IX_Name_Of_Index’ – Index name
       AND I.object_id = OBJECT_ID(‘NameOfSchema.NameOfTable’))
BEGIN
    PRINT ‘Index NOT Exists’
    – Create the index
END

Drop an index before creating a table

DROP INDEX IF EXISTS IX_Name_Of_Index ON NameOfSchema.NameOfTable