T-SQL: OBJECT_ID function

We can use OBJECT_ID function to check whether the object exists in SQL Server db, for example, we can do some safety check, if exists, ignore the creation procedure.

1. Check the sp exists

-- Safety check 
IF OBJECT_ID('usrproc_SharingTempTablesTesting') IS NOT NULL
BEGIN    
    DROP PROCEDURE usrproc_SharingTempTablesTesting; 
END
GO

2. Drop temp table after confirm it exists

IF OBJECT_ID('#SharingTempTables') IS NOT NULL
BEGIN    
    DROP TABLE #SharingTempTables; 
END
GO

3. Check whether the temporary table exists, if not so, just create it

if object_id('tempdb..#TC_TableRelation') is null
begin
    CREATE TABLE [#TC_TableRelation](
        [ID] [nvarchar](255) NOT NULL,
        [ViewName] [nvarchar](50) NOT NULL,
        [MasterTableName] [nvarchar](255) NULL,
        [PrimaryKey] [nvarchar](50) NOT NULL,
        [PrimaryValue] [nvarchar](255) NULL,
        [Status] [nvarchar](255) NULL
    ) ON [PRIMARY]
end

Or we can use this statement

if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#TC_TableRelation') and type='U')
   drop table #TC_TableRelation

4. Get the specified object ID

SELECT 'Outside',OBJECT_ID('#SharingTempTables'),* FROM #SharingTempTables

 

Attention: Important

Objects that are not schema-scoped, such as DDL triggers, cannot be queried by using OBJECT_ID. For objects that are not found in the sys.objects catalog view, obtain the object identification numbers by querying the appropriate catalog view. For example, to return the object identification number of a DDL trigger, use SELECT OBJECT_ID FROM sys.triggers WHERE name = 'DatabaseTriggerLog'.