April 2, 2020
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