Checking if an object exists

In various earlier posts, I’ve included explanations on how to check if an object exists. But those were specific to the type of object in the context of that post.

Here I will include a list of checks for various types of objects, all in one convenient post. Fantastic.

This post will probably be updated as I find checks for other things that may be of use.
Table
IF EXISTS (SELECT * 
    FROM INFORMATION_SCHEMA.TABLES   
    WHERE TABLE_SCHEMA = N'Schema' AND TABLE_NAME = N'myTable')
Temp Table
IF OBJECT_ID('tempdb..#myTable') IS NOT NULL
Column
IF EXISTS(SELECT *
          FROM   INFORMATION_SCHEMA.COLUMNS
          WHERE  TABLE_NAME = 'myTableName'
                 AND COLUMN_NAME = 'myColumnName') 
Index
SELECT * 
FROM sys.indexes 
WHERE name='myIndexName' AND object_id = OBJECT_ID('Schema.myTableName')
Trigger
IF EXISTS (SELECT * 
           FROM sys.triggers 
           WHERE object_id = OBJECT_ID(N'Schema.myTriggerName'))

I hope that helps!

Subscribe

0 comments