OBJECT_ID

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Applies to

• SQL Server

• Azure Synapse Analytics

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

IN -> SqlServer_01.sql

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

Snowflake SQL

2. Unknown database

SQL Server

Code:

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

Snowflake SQL

3. Different object names

SQL Server

Code:

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

Snowflake SQL

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

• SSC-EWI-0001: Unrecognized token on the line of the source code.

• SSC-FDM-0007: Element with missing dependencies