OBJECT_ID

Description

This function returns the database object identification number of a schema-scoped object.(OBJECT_ID in Transact-SQL).

SQL Server syntax

OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . ]   
  object_name' [ ,'object_type' ] )

Sample Source Patterns

1. Default transformation

SQL Server

USE DATABASE2;
IF OBJECT_ID_UDF('DATABASE2.DBO.TABLE1') is not null) THEN
            DROP TABLE IF EXISTS TABLE1;
        END IF;

Snowflake SQL

----** MSC-WARNING - MSCEWI4001 - Transformation for USE <database> is disabled by default. **
--use database1
DROP TABLE IF EXISTS TABLE1;

2. Unknown database

SQL Server

Code:

USE DATABASE2;
IF OBJECT_ID_UDF('DATABASE1.DBO.TABLE1') is not null) THEN
            DROP TABLE IF EXISTS TABLE1;
        END IF;

Snowflake SQL

Code:

----** MSC-WARNING - MSCEWI4001 - Transformation for USE <database> is disabled by default. **
--use database1

 IF (
 --** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'OBJECT_ID_UDF' INSERTED. **
 OBJECT_ID_UDF('DATABASE1.DBO.TABLE1') is not null) THEN
     DROP TABLE IF EXISTS TABLE1;
 END IF;

3. Different object names

SQL Server

Code:

USE DATABASE1;
IF OBJECT_ID_UDF('DATABASE1.DBO.TABLE2') is not null) THEN
            DROP TABLE IF EXISTS TABLE1;
        END IF;

Snowflake SQL

Code:

----** MSC-WARNING - MSCEWI4001 - Transformation for USE <database> is disabled by default. **
--use database1

 IF (
 --** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'OBJECT_ID_UDF' INSERTED. **
 OBJECT_ID_UDF('DATABASE1.DBO.TABLE2') is not null) THEN
     DROP TABLE IF EXISTS TABLE1;
 END IF;

Known issues

1. OBJECT_ID_UDF function has different behavior in certain cases

OBJECT_ID returns the object identification number but the OBJECT_ID_UDF returns a boolean value, so that they are equivalent only when OBJECT_ID is used with not null condition.

Related EWIs

• MSCEWI1020: Custom UDF inserted.

• MSCEWI4001: Transformation for the USE database statement is disabled by default.